sql语句：常用sql查询整理

（文档有待进一步整理，暂时先做备份~）

# 1. 读取降序后前几行的数据
# limit 取前几行
SELECT *
FROM student
ORDER BY age DESC
LIMIT 3


# 2. 查询某列重复的行
SELECT id, name
FROM student
WHERE name in (
		SELECT name
		FROM student
		GROUP BY name
		HAVING(COUNT(*) > 1)
)
ORDER BY `name` DESC;  # 想要排序的话


# 3. 平均分不及格的学生id及平均分
SELECT sid, AVG(score) AS avg_score
FROM stu_course
GROUP BY sid
HAVING avg_score < 60;


# 4. 每门课成绩都不低于80分的学生的id
# distinct去除列中重复记录
# 用到反向思想
SELECT DISTINCT sid
FROM stu_course
WHERE sid not in (
	SELECT sid
	FROM stu_course
	WHERE score < 80
)


# 5. 输出学生姓名和总分
# 方法一：会过滤掉没有成绩的人
SELECT student.`name`, SUM(score) as sum_score
FROM stu_course, student
WHERE student.ID = stu_course.sid
GROUP BY sid

#方法二：更保险的写法是左外连接
SELECT ID, student.`name`, SUM(score) as sum_score
FROM student 
LEFT JOIN stu_course
ON student.ID = stu_course.sid
GROUP BY sid;  # 记得写group by


# 5. 总成绩最高的学生
# 方法一：效率低，会重复计算所有的
SELECT sid, SUM(score) AS sum_score
FROM stu_course
GROUP BY sid
HAVING SUM(score) >= ALL
(SELECT SUM(score) from stu_course GROUP BY sid)

# 方法二：order by 可以使用聚集函数，因此最简单的方法是：
SELECT sid, SUM(score) AS sum_score
FROM stu_course
GROUP BY sid
ORDER BY sum_score DESC
LIMIT 1  # 同理可查前三名


# 6. 查询课程1成绩第2高的学生（查询第N大数的问题）
# 一：先查出第二高的成绩
SELECT score
FROM stu_course
WHERE stu_course.cid = 1
ORDER BY score DESC
LIMIT 1, 1  # 第一个1表示跳过前1行，第二个1表示取之后的1行
# 二：取出改成绩对应的学生
SELECT *
FROM stu_course
WHERE cid = 1 and score = (
	SELECT score
	FROM stu_course
	WHERE stu_course.cid = 1
	ORDER BY score DESC
	LIMIT 1, 1
)


# 7. 查询各科成绩最高的学生，结果列出学生id、课程id和对应的成绩
# 以下方法是不对的，sid是无效的
SELECT sid, cid, MAX(score)
FROM stu_course
GROUP BY cid
# 正确的解法是使用相关嵌套查询(在进行内层查询时需要用到外层查询)：
SELECT * 
FROM stu_course as x
WHERE score >= (
		SELECT MAX(score)
		FROM stu_course as y
		WHERE y.cid = x.cid  # 不是很理解这里为什么这么写？
)


# 8. 查询每门课的前2名，结果按课程id升序，同一门课按成绩降序  -- 取每组的前N条记录
SELECT * 
FROM stu_course as x
WHERE 2 > (SELECT COUNT(DISTINCT score) FROM stu_course as y WHERE x.cid = y.cid and y.score > x.score)  # 如果只有0个、1个学生比x的成绩高，那它一定是前2名
ORDER BY cid, score DESC


# 9. 球队组合比赛，可能的组合  -- 表自己与自己连接
SELECT a.name, b.name
FROM team a, team b
WHERE a.name < b.name


# 10. 查询每个人每门课的成绩
# 一：建一个视图
CREATE VIEW temp AS SELECT student.`name` as sname, course.`name` as cname, score
FROM stu_course join(student, course)
ON student.id = stu_course.sid and course.id = stu_course.cid;
# 二：查询
SELECT sname,  # 注意标点符号必须英文
sum(case when cname='语文' then score else 0 end) as 语文,
sum(case when cname='数学' then score else 0 end) as 数学,
SUM(case when cname='英语' then score else 0 end) as 英语
FROM temp
GROUP BY sname;