开发者社区> 问答> 正文

SQL嵌套查询语句的报错问题?报错

String sql = "select * from msgInfo where ID in(select top 15 ID from msgInfo where chatRoom=? Order by chatTime DESC) order by chatTime";
  String userName=session.getAttribute("_USER").toString();
  PreparedStatement ps = conn.preparedStatement(sql);
  ps.setString(1,session.getAttribute("_CHAT_ROOM").toString());

  ResultSet rs = conn.executeQuery();

这语句有问题吗,怎么老报错,没道理啊

ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '15 ID from msgInfo where chatRoom='dragon inn' Order by chatTime DESC) order by ' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '15 ID from msgInfo where chatRoom='dragon inn' Order by chatTime DESC) order by ' at line 1

展开
收起
爱吃鱼的程序员 2020-06-09 14:43:00 987 0
1 条回答
写回答
取消 提交回答
  • https://developer.aliyun.com/profile/5yerqm5bn5yqg?spm=a2c6h.12873639.0.0.6eae304abcjaIB

    我试到了正确的答案 SELECT*FROMmsginfoWHEREIDIN(SELECTIDFROMmsginfoWHEREchatRoom='dragoninn'ORDERBYchattimeDESC )ORDERBYchattimeLIMIT15;

    谢谢你们第一次到网上问问题

    报错很明显了,15IDfrommsgInfowherechatRoom='dragoninn'OrderbychatTimeDESC)orderby'atline1

    可能是你用的数据库不支持top

    你换成这样应该可以成功select*frommsgInfowhereIDin(selectIDfrommsgInfowherechatRoom=?OrderbychatTimeDESClimit15)orderbychatTime

    即将top15换成在尾部添加limit15

    回复 @助哥的后花园:我公司的时间一般设置成14位的字符串,如:"20160510152300",这种是可以排序的,我不确定datetime类型是否可以排序,你可以去掉排序,然后试下。回复 @求是科技:我上了图片,是不是datatime不能排序..纯sql报错不?抛开传参,你将这段SQL放到sql客户端里面执行,看是否报错,然后根据报错信息拍错limit不支持,TOP支持可是都报错 试试这个select*frommsgInfowhereIDin(selectIDfrommsgInfowherechatRoom=?OrderbychatTimeDESC top15)orderbychatTime

    这样写试试看:

    select类别,sum(数量)as数量之和,摘要

    fromA

    groupby类别,摘要

    orderby类别desc

    如果仍报错请参考资料: http://edu.51cto.com/course/course_id-1107.html

    2020-06-09 14:43:16
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
SQL Server 2017 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载