SQL 经典题型解答(4)

SQL 经典题型解答（4）

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
	a.C,
	a.Cname,
	MAX( b.score ),
	MIN( b.score ),
	CAST( AVG( b.score ) AS DECIMAL ( 18, 2 ) ) pingqunfen,
	CAST(
		( SELECT COUNT( 1 ) FROM sc WHERE sc.C = a.C AND sc.score > 60 ) / ( SELECT COUNT( 1 ) FROM sc WHERE sc.C = a.C ) AS DECIMAL ( 18, 2 ) 
	) jigelv,
	CAST(
		(
		SELECT
			COUNT( 1 ) 
		FROM
			sc 
		WHERE
			sc.C = a.c 
			AND sc.score >= 70 
			AND sc.score < 80 
		) / ( SELECT COUNT( 1 ) FROM sc WHERE sc.c = a.c ) AS DECIMAL ( 18, 2 ) 
	) zhongdneglv,
	CAST(
		(
		SELECT
			COUNT( 1 ) 
		FROM
			sc 
		WHERE
			sc.C = a.C 
			AND sc.score >= 80 
			AND sc.score < 90 
		) / ( SELECT COUNT( 1 ) FROM sc WHERE sc.C = a.C ) AS DECIMAL ( 18, 2 ) 
	) youlianglv,
	CAST(
		( SELECT COUNT( 1 ) FROM sc WHERE sc.C = a.C AND sc.score >= 90 ) / ( SELECT COUNT( 1 ) FROM sc WHERE sc.C = a.C ) AS DECIMAL ( 18, 2 ) 
	) youxiulv 
FROM
	course a,
	sc b 
WHERE
	a.C = b.C 
GROUP BY
	a.C,
	a.Cname 
ORDER BY
		a.C 

详解：

SQL 不同于与其他编程语言的最明显特征是处理代码的顺序。在大数编程语言中，代码按编码顺序被处理，但是在 SQL 语言中，第一个被处理的子句是 FROM 子句，尽管 SELECT 语句第一个出现，但是几乎总是最后被处理。 每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回 给调用者
上面代码的运行顺序如下：

1. FROM：对 FROM 子句中的前两个表执行笛卡尔积（ Cartesian product )(交叉联接），生成虚拟表 VT1
2. WHERE：对 VT1 应用 WHERE 筛选器。只有使为 TRUE 的行才被插入 VT2 .
3. GROUP BY：按 GROUP BY 子句中的列列表对 VT2 中的行分组，生成 VT3 .
4. SELECT：处理 SELECT 列表，产生 VT4 .
5. ORDER BY：将 VT4 中的行按 ORDER BY 子句中的列列表排序，生成 VT5,并返回调用者.

• CAST(expr AS type)函数用于将 expr 源值转换为目标数据类型 type
• CAST() 函数中的SELECT 语句需要通过 WHERE sc.C = a.C 进行限制，因为已经通过 GROUP BY 语句对表 a 进行了分组，如果不加限制，算出的结果没有对课程进行分组。
  代码中的 COUNT( 1 )表示求表 sc 的第一列数量，相当于 COUNT( s )

参考资料：

• SQL语句操作优先级顺序
• SQL CAST 用法

程序运行结果：

---

19、按各科成绩进行排序，并显示排名

SELECT
	a.s,
	a.sname,
	c.Cname,
	b.score,
	RANK ( ) over ( ORDER BY b.score DESC) AS '名次'
FROM
	student a,
	sc b,
	course c 
WHERE
	a.s = b.S 
	AND b.c = c.C 
	AND b.C = '01' 

详解：

RANK() 函数用法：RANK() OVER(order by [目标列名]) as name,根据目标列名所在列进行排序，返回排名。
其他科目程序与上面类似。
SQL 四大排名函数

程序运行结果：

---

部分答案参考自：SQL 经典五十道题