准备查询用表
学生表(student)
班级表(class)
成绩表(score)
1、查询每个班里成绩最高的学生:
分析: 先使用group by对表按照班级切分,然后使用max得出成绩最高的数据!
SELECT s.`name`,s.`stuId`,s.`sex`,c.`className`,r.`course`,MAX(r.`core`) AS core FROM
student s
INNER JOIN class c ON s.`classId` = c.`classId`
INNER JOIN score r ON s.`stuId` = r.`stuId`
GROUP BY c.`classId`
2、查询每个班里成绩第二的学生:
分析: 先查出每个班成绩第一的学生,然后使用Not in 查出除去每个班成绩第一的学生,最后查出剩下的学生中每个班成绩最高的学生。
SELECT NAME,stuId,sex,className,course,MAX(core) AS two FROM (
SELECT s.`name`,s.`stuId`,s.`sex`,c.`className`,r.`course`,r.`core` FROM
student s
INNER JOIN class c ON s.`classId` = c.`classId`
INNER JOIN score r ON s.`stuId` = r.`stuId`
) a WHERE core NOT IN (
SELECT MAX(r.`core`) AS core FROM
student s
INNER JOIN class c ON s.`classId` = c.`classId`
INNER JOIN score r ON s.`stuId` = r.`stuId`
GROUP BY c.`classId`
)
GROUP BY a.className
3、按照班级查询每个班上总分前三的学生:
SELECT * FROM(
SELECT s.`name`,s.`stuId`,s.`sex`,c.`className`,r.`course`,r.`core` FROM
student s
INNER JOIN class c ON s.`classId` = c.`classId`
INNER JOIN score r ON s.`stuId` = r.`stuId`
) a WHERE (
SELECT COUNT(*) FROM (
SELECT s.`name`,s.`stuId`,s.`sex`,c.`className`,r.`course`,r.`core` FROM
student s
INNER JOIN class c ON s.`classId` = c.`classId`
INNER JOIN score r ON s.`stuId` = r.`stuId`
) b WHERE a.className = b.className AND a.core < b.core
) <3
ORDER BY a.className,a.core DESC;
4、分数排序后排名在2-8的学生
SELECT * FROM score
ORDER BY core DESC LIMIT 1,7
5、按分数进行排名,排名从1开始,分数相同排名相同
//查询去重后分数的,条件:当前行分数大于等于同表的分数的count数量,去重显示,然后降序输出
SELECT core,
(SELECT COUNT(DISTINCT core)
FROM score
WHERE core>=s.core) AS Rank
FROM score AS s
ORDER BY core DESC;
7、用一条sql语句取出所有姓名重复的学生姓名和重复的记录数
SELECT NAME,COUNT(1) AS rnum FROM student
GROUP BY NAME
HAVING COUNT(1)>=2
ORDER BY rnum DESC
更多查询语句请访问原文链接:常用SQL语句汇总
- 感谢您的关注和阅读,在您阅读本文有任何疑问和想法,欢迎和我们联系。
- 文章首发自公众号(Web小项目)
- 专注javaweb项目的源码分享,和各种场景案例的解决方案以及Java资源分享。
- 扫描下方二维码关注我们