|
8.统计列印各科成绩,各分数段人数:
课程ID,课程名称,[100-85],[85-70],[70-60],[<60]
尽管表面看上去不那么容易,其实用 CASE 可以很容易地实现:
SELECT 课程ID, 课程名称
,SUM(CASE WHEN 成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN 成绩 BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN 成绩 BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN 成绩 < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM 成绩表
GROUP BY 课程ID, 课程名称
注意这里的 BETWEEN,虽然字段名都是从高至低,可 BETWEEN 中还是要从低到高,这里
如果不小心,会犯一个很难发现的逻辑错误: 在数学上,当a > b 时,[a, b]是一个空集。
9.列印学生平均成绩及其名次
select count(distinct b.f) as 名次,a.学生ID,max(a.学生姓名),max(a.f)
from (select distinct t.学生ID,t.学生姓名,(select avg(成绩)
from t t1
where t1.学生id = t.学生id) as F
from T
) as a,
(select distinct t.学生ID,t.学生姓名,(select avg(成绩)
from t t1
where t1.学生id = t.学生id) as F
from T
) as b
where a.f <= b.f
group by a.学生ID
order by count(b.f)
这里有很多值得一提的地方,先利用两个完全相同的自相关子查询生成两个派生表作
为基本表用于作小于或等于的连接,这样就可以通过表中小于或等于每个值的其他值
的 COUNT(distinct) 的计数聚集函数来体现名次了。
SELECT 1+(SELECT COUNT(distinct [平均成绩])
FROM (SELECT [学生ID],MAX([学生姓名]) AS 学生姓名 ,AVG([成绩]) AS [平均成绩]
FROM T
GROUP BY [学生ID]
) AS T1
WHERE [平均成绩] > T2.[平均成绩]) as 名次,
[学生ID],[学生姓名],[平均成绩]
FROM (SELECT [学生ID],max([学生姓名]) AS 学生姓名,AVG([成绩]) AS [平均成绩]
FROM T
GROUP BY [学生ID]
) AS T2
ORDER BY T2.[平均成绩] desc
方法二也使用了两个完全相同的自相关子查询生成两个派生表作为基本表,再利用它
们之间作大于的相关子查询取 COUNT(distinct) + 1 的计数聚集函数同样实现了名
次的显示。
这道题从应用角度来看,查询结果是相当合理的,并列情况的名次也都一样。但如果想
实现类似自动序列的行号,该解决方案的局限性突显,不能处理并列相等的情况了,所
以有必要强调:一定要选择不重复的连接条件,可以根据实际情况利用字段组合的不等
连接 (T1.f1 + ... + T1.fn <= T2.f1 + ... + T2.fn)。继续引申还可以通过判断
COUNT(distinct) % 2 是否为 0 的 HAVING 或 WHERE 子句实现只显示偶数或奇数行:
HAVING count(distinct b.f) % 2 = 1
或:
WHERE 1+(SELECT COUNT(distinct [平均成绩])
FROM (SELECT [学生ID],MAX([学生姓名]) AS 学生姓名 ,AVG([成绩]) AS [平均成绩]
FROM T
GROUP BY [学生ID]
) AS T1
WHERE [平均成绩] > T2.[平均成绩]) % 2 =1
再简单说一下 HAVING 和 WHERE 在含有 GROUP BY 分组的查询中的区别,HAVING 是
在数据分组后才筛选记录的,WHERE 是先进行筛选在分组的,而且 HAVING 一般应与聚
集函数合用才有真正含义。
两种方法再次体现了子查询与连接可以殊途同归之妙,第二种子查询方法值得推荐,因
为比较利于程序构造,便于为没有该功能的原有查询添加此项功能。本题仅仅是为了示
范一种比较新颖的解题思路,回避了效率的问题。
10.列印各科成绩前三名的记录:(不考虑成绩并列情况)
学生ID,学生姓名,课程ID,课程名称,成绩,教师ID,教师姓名
如果仅从成绩考虑前三名的人,利用相关子查询的知识:
SELECT *
FROM 成绩表 t1
WHERE 成绩 IN (SELECT TOP 3 成绩
FROM 成绩表
WHERE t1.课程id = 课程id
ORDER BY 成绩 DESC
)
ORDER BY t1.课程id
这样查询的结果各科成绩前三名的记录数应该大于等于三,因为可能有并列情况,
如果小于三自然是该门课还没有那么多人考试!
如果不考虑并列情况,严格控制各科只列印三条记录,则使用"学生id"构造相关
子查询条件亦可:
SELECT *
FROM 成绩表 t1
WHERE 学生id IN (SELECT TOP 2 学生id
FROM 成绩表
WHERE t1.课程id = 课程id
ORDER BY 成绩 DESC
)
ORDER BY t1.课程id
如果利用第 10 题的思路也可实现该应用。
11.规范化
规范化的问题可以说是仁者见仁,智者见智。而且不做肯定不好,但过犹不及,搞到太
规范也不一定是好事。首先分析信息的对应关系,这个表中有四种信息。学生、课程、教师、成绩。其中前三个可以独立存在,最
后一个可以看做是基于前三个存在的。然后,我们按这四种分类,建立四个表:
关于学生的信息,有以下两个:学生ID,姓名;
教师则会有教师ID,姓名,课程ID 这也就是为什么我要把学生和教师会为两个表的原因;
课程则有课程ID,课程名称两种;
而最后一个成绩信息,就成为了联接它们的一个部分,在这里,它要有学生ID,教师ID,课程ID,成绩四项,相
对与其它表应属应用级别,除了成绩字段,其它都引用的另外的表。
这样一来,几个表的脚本大概是这个样子:
CREATE TABLE "学生信息"
(
"ID" CHAR(4),
"姓名" CHAR(16),
PRIMARY KEY ("ID")
)
CREATE TABLE "课程信息"
&nbs [1] [2] [3] [4] [5] [6] 下一页
[办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE
[办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全
[办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法
[办公软件]Linux程序员必读：中文化与GB18030标准 [办公软件]linux指令大全
[办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
