2 qq 28247769 qq_28247769 于 2016.05.10 13:52 提问

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

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

9个回答

qq_33508850
qq_33508850   2016.05.10 14:29

一,Mysql 应该是不支持top关键词的,你应该用limit
二.limit 关键词不能和 in 一起使用,所以你要在limit子查询外再嵌套一层子查询.

qq_28247769
qq_28247769 不会写,能帮我改下吗
一年多之前 回复
Royal_lr
Royal_lr   Ds   Rxr 2016.05.10 15:08

一楼正解,mysql去前几行的是limit 0,3,,是0行到第3行的数据

qq_33508850
qq_33508850   2016.05.10 15:35

SELECT *
FROM msgInfo
WHERE ID IN (
SELECT id
FROM (
SELECT ID
FROM msgInfo
WHERE chatRoom = ?
ORDER BY chatTime DESC
limit 15
)
) AS tmp
ORDER BY chatTime


qq_33508850
qq_33508850 我想粘贴出缩进好的格式的啊,怎么粘贴进来 缩进都没了啊
一年多之前 回复
baidu_23086307
baidu_23086307   2016.05.10 15:40

一楼一楼,他的可以的

qq_33508850
qq_33508850   2016.05.10 16:25
SELECT *
FROM msgInfo
WHERE ID IN (
        SELECT id
        FROM (
            SELECT ID
            FROM msgInfo
            WHERE chatRoom = ?
            ORDER BY chatTime DESC 
            limit 15
            )
        ) AS tmp
ORDER BY chatTime
qq_33508850
qq_33508850 还是markdown 语法比较靠谱
一年多之前 回复
qq_33508850
qq_33508850 还是markdown 语法比较靠谱
一年多之前 回复
qq_28247769
qq_28247769   2016.05.10 18:30

我试到了正确的答案SELECT * FROM msginfo WHERE ID IN(SELECT ID FROM msginfo WHERE chatRoom='dragon inn' ORDER BY chattime DESC ) ORDER BY chattime LIMIT 15; 谢谢你们这么热心帮我

qq_28247769
qq_28247769   2016.05.10 18:30

我试到了正确的答案SELECT * FROM msginfo WHERE ID IN(SELECT ID FROM msginfo WHERE chatRoom='dragon inn' ORDER BY chattime DESC ) ORDER BY chattime LIMIT 15; 谢谢你们这么热心帮我

yicp123
yicp123   2016.05.10 21:08

SELECT * FROM msgInfo t
WHERE EXISTS (
SELECT ID
FROM (
SELECT ID FROM msgInfo WHERE chatRoom = ? ORDER BY chatTime DESC limit 15)
) TMP
WHERE TMP.ID=T.ID
ORDER BY T.chatTime;
可以这样写,用exists代替in,因为mysql 会自动将in 解析为exists,所以,从优化的角度用exists比in更合适
关于嵌套查询如何优化,有兴趣可以看看 http://www.data.5helpyou.com/article272.html

qq_28247769
qq_28247769   2016.05.11 17:03

还是我太年轻了。。。我的是错的,吗,没有输出出最新的记录,还是他的对的,就是把AD tmp放前面去一点就好了,尴尬了

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!