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

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

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

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

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

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

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