SQL 中的ORDER BY

上进的菜鸟

于 2019-05-12 20:22:34 发布

阅读量894

点赞数

CC 4.0 BY-SA版权

分类专栏： Datawhale之MySQL

本文链接：https://blog.youkuaiyun.com/kylin_learn/article/details/90144530

Datawhale之MySQL 专栏收录该内容

43 篇文章

订阅专栏

本文探讨了MySQL中IN与LIMIT结合使用的限制及解决方案，包括通过额外嵌套查询和使用表表达式的方法来规避限制，同时展示了如何不使用LIMIT获取指定排名记录的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL中 IN 不能和LIMIT一起用

还是上jie那个学生表，我现在想查询cid为02 的前三名学生的总成绩

SELECT SUM(s_score)
  FROM score
 WHERE s_score IN (SELECT s_score
FROM score
 WHERE c_id = '02'
 ORDER BY s_score DESC
 LIMIT 3);

this version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

解决方案1：再嵌套一次

SELECT SUM(s_score)
  FROM score
 WHERE s_score IN (SELECT s_score FROM(
				SELECT s_score
				 FROM score
				WHERE c_id = '02'
				ORDER BY s_score DESC
				LIMIT 3) AS t1);

解决方案2：把它放在from里面不涉及in

SELECT SUM(t1.s_score)
  FROM (
SELECT s_score
FROM score
 WHERE c_id = '02'
 ORDER BY s_score DESC
 LIMIT 3) t1;

当然我也可以不用limit

-- cid的为02的前三名
SELECT SUM(t1.s_score)
  FROM (
SELECT s1.s_id,s1.s_score
  FROM Score s1
 WHERE s1.c_id = '02'
   AND (SELECT COUNT(*) FROM Score s2
		 WHERE s1.c_id = s2.c_id
           AND s2.s_score > s1.s_score) < 3) AS t1;

我也不知道为什么以前说表表达式不能用order by但这里确实可以了

等待大神回复