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

助哥的后花园 发布于 2016/05/10 13:51
阅读 201
收藏 0
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

加载中
0
助哥的后花园
助哥的后花园

我试到了正确的答案SELECT * FROM msginfo WHERE ID IN(SELECT ID FROM msginfo WHERE chatRoom='dragon inn' ORDER BY chattime DESC   ) ORDER BY chattime LIMIT 15;

谢谢你们第一次到网上问问题
0
求是科技
求是科技

报错很明显了,15 ID from msgInfo where chatRoom='dragon inn' Order by chatTime DESC) order by ' at line 1

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

你换成这样应该可以成功 select * from msgInfo where ID in(select ID from msgInfo where chatRoom=? Order by chatTime DESC limit 15) order by chatTime

即将top 15 换成在尾部添加limit 15

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

这样写试试看:

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

from A

group by 类别,摘要

order by 类别 desc

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

返回顶部
顶部