面试的时候碰到一个笔试题,一张学生成绩表A(表可以自行脑补,就这个意思),如图(学生,课程,分数):
要查出总分排名前三的学生成绩信息。
首先是要查出总分前三的学生:
SELECT name from a GROUP BY name ORDER BY sum(fenshu) DESC LIMIT 0,3 ;
这样,发现并没有什么问题。
于是想想,加个in 不就行了,
SELECT * from a WHERE name in (SELECT name from a GROUP BY name ORDER BY sum(fenshu) DESC LIMIT 0,3 );
点击运行,
[SQL] SELECT * from a WHERE name in (SELECT name from a GROUP BY name ORDER BY sum(fenshu) DESC LIMIT 0,3 )
[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
这不能连着用怎么办。。。。
一翻尝试,发现,把limit 包起来,居然可以连用了:
SELECT * FROM `a` where name in ( SELECT name from (SELECT name from a GROUP BY name ORDER BY sum(fenshu) desc LIMIT 0,3) as c );
这是什么道理,有小伙伴可以解释下么?