SQL 经典题型解答(5)

SQL 经典题型解答（5）

@(数据库)

20、查询学生的总成绩并进行排名

SELECT
	a.s,
	a.Sname,
	SUM( b.score ) AS sumscore,
	RANK() OVER (ORDER BY SUM( b.score ) DESC) AS '名次'
FROM
	student a,
	sc b 
WHERE
	a.s = b.S 
GROUP BY
	a.S,a.Sname

详解：

没有用到新的知识。

程序运行结果：

---

21、查询不同老师所教不同课程平均分从高到低显示

SELECT
	a.C,
	a.Cname,
	c.Tname,
	avg( b.score ) AS avgscore 
FROM
	course a,
	sc b,
	teacher C 
WHERE
	a.C = b.C 
	AND a.T = c.T 
GROUP BY
	a.c,
	a.Cname,
	c.Tname 
ORDER BY
	avgscore DESC

程序运行结果：

---

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT
	a.*,
	b.c,
	b.score 
FROM
	student a,
	(
	SELECT
		* 
	FROM
		( SELECT s, c, score, ROW_NUMBER ( ) OVER ( ORDER BY score DESC ) AS mc FROM sc WHERE C = '01' ) a 
	WHERE
		mc BETWEEN 2 
		AND 3 UNION ALL
	SELECT
		* 
	FROM
		( SELECT s, c, score, ROW_NUMBER ( ) OVER ( ORDER BY score DESC ) AS mc FROM sc WHERE C = '02' ) b 
	WHERE
		mc BETWEEN 2 
		AND 3 UNION ALL
	SELECT
		* 
	FROM
		( SELECT s, c, score, ROW_NUMBER ( ) OVER ( ORDER BY score DESC ) AS mc FROM sc WHERE C = '03' ) c 
	WHERE
		mc BETWEEN 2 
		AND 3 
	) b 
WHERE
	a.s = b.s 
ORDER BY
	c

详解；

通过查询表 sc 可以根据每一个课程的成绩生成表格。然后查出排名在 2 和 3 之间的学生的学号、课程、以及课程成绩，在联合表 stuedent 得到血色和功能的信息。

由于课程较少，可以采用这种方法，但是如果课程数多就不可以，应该采用 SQL 循环，
如果题目要求中没有声明选取课程几，代码中就不应该出现 ’01‘，’02‘，’03’ 这样的课程。

SQL UNION 操作符用法

程序运行结果：

---

解法二：

SELECT
	a.*,
	b.c,
	b.score,
	b.mc 
FROM
	student a,
	( SELECT *, ROW_NUMBER ( ) OVER ( PARTITION BY c ORDER BY score DESC ) AS mc FROM sc ) b 
WHERE
	a.s = b.s 
	AND b.mc BETWEEN 2 
	AND 3 
ORDER BY
	C

详解：

这个程序中运用了 PARTITION BY 函数，程序中意为先将表 sc 按照 c 进行分组，然后对每一个分组用 ORDER BY 进行排序。最后通过 ROW_NUMBER ( ) 函数生成排名。这样就不需要考虑总共有几门课程了。
PARTITION BY 用法

程序运行结果：