看完测试完下面这些试题，你的ｓｑｌ水平一定会有新的提高。
下面我们先看一下题设:
二维表 t(f1,f2,f3,f4,f5,f6,f7) 表示如下关系:
┌─────┬────┬─────┬─────┬─────┬─────┬─────┐
│ 学生id │学生姓名│ 课程id │ 课程名称 │ 成绩 │ 教师id │ 教师姓名 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s3 │ 王五 │ k4 │ 政治 │ 53 │ t4 │ 赵老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 张三 │ k1 │ 数学 │ 61 │ t1 │ 张老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s2 │ 李四 │ k3 │ 英语 │ 88 │ t3 │ 李老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 张三 │ k4 │ 政治 │ 77 │ t4 │ 赵老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s2 │ 李四 │ k4 │ 政治 │ 67 │ t5 │ 周老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s3 │ 王五 │ k2 │ 语文 │ 90 │ t2 │ 王老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s3 │ 王五 │ k1 │ 数学 │ 55 │ t1 │ 张老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 张三 │ k2 │ 语文 │ 81 │ t2 │ 王老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s4 │ 赵六 │ k2 │ 语文 │ 59 │ t1 │ 王老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 张三 │ k3 │ 英语 │ 37 │ t3 │ 李老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s2 │ 李四 │ k1 │ 数学 │ 81 │ t1 │ 张老师 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ .... │ │ │ │ │ │ │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ .... │ │ │ │ │ │ │
└─────┴────┴─────┴─────┴─────┴─────┴─────┘
为便于大家更好的理解,我们将 t 表起名为"成绩表"
1.如果 t 表还有一字段 f 数据类型为自动增量整型(唯一,不会重复),
而且 t 表中含有除 f 字段外,请删除其它字段完全相同的重复多余的脏记录数据:
本问题就是一个清理"逻辑重复"记录的问题,当然,这种情况完全可以利用主键约束来
杜绝!然而,现实情况经常是原始数据在"洗涤"后,方可安全使用,而且逻辑主键过早的
约束,将会给采集原始数据带来不便,例如:从刷卡机上读取考勤记录。到了应用数据
的时候,脏数据就该被扫地出门了! 之所以题中要保留一个自动标识列,是因为它的确
是下面答案所必须的前提:
delete l
from "成绩表" l
join "成绩表" r
on l."学生id" = r."学生id" and l."课程id" = r."课程id" and l.f > r.f
这是思路最精巧且最直接有效的方法之一。用不等自联接,正好可以将同一组重复数
据中 f 字段值最小的那一条留下,并选出其它的删掉,如果只有一条,自然也不会被选
中了。这里还要强调一下,大家一定要分清楚被操作的基本表也就是 delete 关键字
后的表和过滤条件所使用的由基本表连接而成的二维表数据集,也就是 from 子句的
全部。在自连接的 from 子句至少要取一个别名来引用基本表。别名的使用在编写大
量类似结构的 sql 时非常方便,而且利于统一程序构造动态 sql。如有必要加强条件,
还可继续使用 where 子句。如果上面的例子还不够直观,下面模仿一个不等自联接,
有一组数 (1,2,3),作一个不等自联接,令左子集大于右子集,是:
2 1
3 1
3 2
如果现在选出左子集,就是 2 和 3 了。1 在右边没有比它更小的数据可以与之匹配,
因此被过滤了。如果数据大量重复,效率会差强人意,幸亏不是 select ,而是 delete
无需返回结果集,影响自然小多了。
delete t
from 成绩表 t
where f not in (select min(f)
from 成绩表 i
group by i.学生id,i.课程id
having count(*)>1
)
and f not in (select min(f)
from 成绩表 i
group by i.学生id, i.课程id
having count(*)=1
)
这种方法思路很简单,就像翻译自然语言,很精确地描述了符合条件记录的特性,甚至
第二个条件的确多余。至少应该用一个 >= 号合并这两个条件或只保留任意一个条件,
提高效率。
delete t
from 成绩表 t
where f > (select min(f)
from 成绩表 as i
where i.学生id = t.学生id
and i.课程id = t.课程id
group by i.学生id, i.课程id
)
这种方法,基本上是方法一的相关子查询版本,了解笛卡尔积的读者能会好理解些,而
且用到了统计函数,因此效率不是太高。细心的读者会发现子查询里的 group by 子
句没有必要,去掉它应该会提高一些效率的。
关于 delete 语句的调试,有经验的程序员都会先用无害的 select 暂时代替危险的
delete。例如:
select l.*
--delect l 暂时注释掉
from "成绩表" l
join "成绩表" r
on l."学生id" = r."学生id" and l."课程id" = r."课程id" and l.f>r.f
这样,极大地减小了在线数据被无意破坏的可能性,当然数据提前备份也很重要。同理
update 和 insert 写操作也应照此行事。从数据库原理的关系运算的角度来看 insert、
update 和 delete 这些写操作都属于典型的"选择(selection)"运算,update 和 insert
而且还是"投影(projection)"运算,它们都是这些关系运算的"写"应用的表现形式。
其实,查询的目的也本来无非就是浏览、删除、更
新或插入。通常写操作也比读操作消耗更大,如果索引过多,只会降低效率。
选择"子查询"还是"连接"在效率是有差别的,但最关键的差别还是表现在查询的结果
集的读写性上,开发人员在写一个"只读"应用的查询记录集时,"子查询"和"连接"各自
的效率就是应该首先考虑的问题,但是如果要实现"可写"应用的查询结果集,则无论是
相关还是非相关子查询都是在复杂应用中难以避免的。
以上解决方案中,应该说第一种方法,简洁有效,很有创意,是值得推荐的方法。当然,
最简单的写法应该是:
delete t
from t,t t1
where t.学生id=t1.学生id and t.课程id=t.课程id and t.f < t1.f
其实这就是方法一的"标准"(但确实实不是《ansi/iso sql》标准)连接写法,以下各
题答案为了便于读者理解,一般不采用这种写法,这也是《ansi/iso sql》标准所鼓
励的,join 确实更容易地表达表之间的关系,有兴趣的读者可自行改写。如果使用
"*="实现两表以上的外连接时,要注意此时 where 子句的 and 条件是有顺序的,尽
管《ansi/iso sql》标准不允许 where 条件的顺序影响查询结果,但是 from 子句
的各表连接的顺序可以影响查询结果。
2.列印各科成绩最高和最低的相关记录: (就是各门课程的最高、最低分的学生和老师)
课程id,课程名称, 最高分,学生id,学生姓名,教师id,教师姓名, 最低分,学生id,学生姓名,教师id,教师姓名
如果这道题要是仅仅求出各科成绩最高分或最低分,则是一道非常简单的题了:
select l.课程id, max(l.课程名称), max(l.成绩) as 最高分, min(l.成绩) as 最低分
from 成绩表 l
group by l.课程id
但是,刁钻的题目却是要列出各科最高和最低成绩的相关记录,这也往往才是真正需求。
既然已经选出各科最高和最低分,那么,剩下的就是把学生和教师的信息并入这个结果
集。如果照这样写下去,非常麻烦,因为要添加的字段太多了,很快就使代码变得难于
管理。还是换个思路吧:
select l.课程id,l.课程名称,l.[成绩] as 最高分,l.[学生id],l.[学生姓名],l.[教师id],l.[教师姓名]
,r.[成绩] as 最低分,r.[学生id],r.[学生姓名],r.[教师id],r.[教师姓名]
from 成绩表 l
join 成绩表 as r on l.[课程id] = r.[课程id]
where l.[成绩] = (select max(il.[成绩])
from 成绩表 as [il]
where l.[课程id] = il.[课程id]
group by il.[课程id]
)
and
r.[成绩] = (select min(ir.[成绩])
from 成绩表 as [ir]
where r.[课程id] = ir.[课程id]
group by ir.[课程id]
)
乍一看答案,好像很复杂,其实如果掌握了构造交叉透视表的基本方法和相关子查询的
知识,问题迎刃而解。由于最低和最高分都是针对课程信息的,该答案巧妙地把课程信
息合并到了最高分的数据集中,当然也可以合并到最低分中。代码中规中矩,风格很好,
可读性也是不错的。
3.按平均成绩从高到低顺序,列印所有学生的四门(数学,语文,英语,政治)课程成绩: (就是每个学生的四门课程的成绩单)
学生id,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分
(注: 有效课程即在 t 表中有该学生的成绩记录,如不明白可不列印"有效课程数"和"有效平均分")
需要说明的是: 题目之所以明确提出"四门(数学,语文,英语,政治)课程"是有道理的,
因为实现时,的确无法避免使原基本表中的行上的数据的值影响列,这又是一个典型的
"行变列"的相关子查询:
select 学生id,max(学生姓名) as 学生姓名,
(select 成绩 from 成绩表 where 学生id=t.学生id and 课程id='k1') as 数学 ,
(select 成绩 from 成绩表 where 学生id=t.学生id and 课程id='k2') as 语文 ,
(select 成绩 from 成绩表 where 学生id=t.学生id and 课程id='k3') as 英语 ,
(select 成绩 from 成绩表 where 学生id=t.学生id and 课程id='k4') as 政治 ,
count(*) as 有效课程数, avg(t.成绩) as 平均成绩
from 成绩表 as t
group by 学生id
order by 平均成绩
这可以说也是一个很规矩的解法,在这种应用场合,子查询要比联接代码可读性强得多。
如果数据库引擎认为把它解析成联接更好,那就由它去吧,其实本来相关子查询也肯定含有连接。
这里再补充一下,在实际应用中如果再加一张表 ranks(rank,minvalue,maxvalue):
┌─────┬─────┬─────┐
│ rank │ minvalue │ maxvalue │
├─────┼─────┼─────┤
│ a │ 90 │ 100 │
├─────┼─────┼─────┤
│ b │ 80 │ 89 │
├─────┼─────┼─────┤
│ c │ 70 │ 79 │
├─────┼─────┼─────┤
│ d │ 60 │ 69 │
├─────┼─────┼─────┤
│ e │ 0 │ 59 │
└─────┴─────┴─────┘
就可以实现一个非常有实用价值的应用:
select 学生id,max(学生姓名) as 学生姓名
,(select 成绩 from 成绩表 t where 学生id=t0.学生id and 课程id='k1') as 数学
,(select max(rank) from ranks ,成绩表 t
where t.成绩 >= ranks.minvalue
and t.成绩 <= ranks.maxvalue
and t.学生id=t0.学生id and t.课程id='k1'
) as 数学级别
,(select 成绩 from 成绩表 t where 学生id=t0.学生id and 课程id='k2') as 语文
,(select min(rank)
from ranks ,成绩表 t
where t.成绩 >= ranks.minvalue
and t.成绩 <= ranks.maxvalue
and t.学生id=t0.学生id and t.课程id='k2'
) as 语文级别
,(select 成绩 from 成绩表 t where 学生id=t0.学生id and 课程id='k3') as 英语
,(select max(rank)
from ranks ,成绩表 t
where t.成绩 >= ranks.minvalue
and t.成绩 <= ranks.maxvalue
and t.学生id=t0.学生id and t.课程id='k3'
) as 英语级别
,(select 成绩 from 成绩表 t where 学生id=t0.学生id and 课程id='k4') as 政治
,(select min(rank)
from ranks ,成绩表 t
where t.成绩 >= ranks.minvalue
and t.成绩 <= ranks.maxvalue
and t.学生id=t0.学生id and t.课程id='k4'
) as 政治级别
,count(*),avg(t0.成绩)
,(select max(rank)
from ranks
where avg(t0.成绩) >= ranks.minvalue
and avg(t0.成绩) <= ranks.maxvalue
) as 平均级别
from 成绩表 t0
group by 学生id
这里表面上使用了不等连接,再仔细想想,ranks 表中每条记录的区间是没有交集的,
其实也可以认为是等值连接,这样的表设计无疑存在着良好的扩展性,如果题目只要求
列印(学生id,学生姓名,有效课程数,有效平均分,平均分级别):
select 学生id,max(学生姓名) as 学生姓名,count(*),avg(t0.成绩)
,(select max(rank)
from ranks
where avg(t0.成绩) >= ranks.minvalue
and avg(t0.成绩) <= ranks.maxvalue
) as 平均级别
from t as t0
group by 学生id
则这样的解决方案就比较全面了。
回到原题,再介绍一个比较取巧的办法,仅需一个简单分组查询就可解决问题,有经验的读者可能已经想到了
,那就是 case:
select 学生id, min(学生姓名),
sum(case 课程id when 'k1' then 成绩 else 0 end) as 数学,
sum(case 课程id when 'k2' then 成绩 else 0 end) as 语文,
sum(case 课程id when 'k3' then 成绩 else 0 end) as 英语,
sum(case 课程id when 'k4' then 成绩 else 0 end) as 政治,
count(*) as 有效课程数, avg(t.成绩) as 平均成绩
from 成绩表 as t
group by 学生id
order by 平均成绩 desc
虽然可能初看答案感觉有点怪,其实很好理解,可读性并不低,效率也很高。但它不能
像前一个答案那样,在成绩中区分出某一门课这个学生究竟是缺考 (null),还是真得
零分。这个解法充分利用了 case 语句进行数据分类的作用: case 将成绩按课程分
成四类,sum 用来消去多余的 0。
select [t].[学生id],max([t].[学生姓名]) as 学生姓名,
max([t1].[成绩]) as 数学,
max([t2].[成绩]) as 语文,
max([t3].[成绩]) as 英语,
max([t4].[成绩]) as 政治,
count([t].[课程id]) as 有效课程数,
(isnull(max([t1].[成绩]),0) +
isnull(max([t2].[成绩]),0) +
isnull(max([t3].[成绩]),0) +
isnull(max([t4].[成绩]),0)) / count([t].[课程id]) as 有效平均分
from 成绩表 t
left join 成绩表 as [t1] on [t].[学生id] = [t1].[学生id] and [t1].[课程id] = 'k1'
left join 成绩表 as [t2] on [t].[学生id] = [t2].[学生id] and [t2].[课程id] = 'k2'
left join 成绩表 as [t3] on [t].[学生id] = [t3].[学生id] and [t3].[课程id] = 'k3'
left join 成绩表 as [t4] on [t].[学生id] = [t4].[学生id] and [t4].[课程id] = 'k4'
group by [t].[学生id]
order by 有效平均分 desc
这个方法是相当正统的联接解法,尽管写起来麻烦了些,但还是不难理解的。再从实用
角度考虑一下,真实需求往往不是象本题明确提出"列印四门 (数学,语文,英语,政治)
课程"这样的相对静态的需求,该是动态 sql 大显身手的时候了,很明显方法一的写法
无疑是利用程序构造动态 sql 的最好选择,当然另两个 sql 规律还是挺明显的,同样
不难构造。以 case 版答案为例: 先用一个游标遍历,取出所有课程凑成:
sum(case '课程id' when '课程名称' then 成绩 else 0 end) as 课程名称 形式,
再补上 select 和 from、where 等必要条件,一个生成动态成绩单的 sql 就诞生了,
只要再由相关程序调用执行即可,这样就可以算一个更完善的解决方案了。
其实,最类似的典型应用是在主、细关系中的主表投影中实现细表的汇总统计行,
例如两张表:
master(f,f1,f2 ...) 一对多 details(f,f3,f4 ...)
select *
,( select count(*)
from details
where master.f = details.f
)
,( select sum(f3)
from details
where master.f = details.f
)
from master
4.按各科不平均成绩从低到高和及格率的百分数从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"n行"表示):
(就是分析哪门课程难)
课程id,课程名称,平均成绩,及格百分比
select 课程id,max(课程名称) as 课程名称,avg(成绩) as 平均成绩
,str(100 * sum(case when 成绩 >=60 then 1 else 0 end)/count(*))+'%' as 及格百分比
from 成绩表 t
group by 课程id
order by 及格百分比 desc
这道题应该说是算简单的了,就是用"行"来提供表现形式的。只要想明白要对数据如
何分组,取统计聚集函数,就万事大吉了。
5.列印四门课程平均成绩和及格率的百分数(用"1行4列"表示): (就是分析哪门课程难)
数学平均分,数学及格百分数,语文平均分,语文及格百分数,英语平均分,英语及格百分数,政治平均分,政治及格百分数
这道题其实就是上一题的"列"表现形式版本,相对于上一题,本题是静态的,因为本题
同第三题一样利用行上的数据构造了列,要实现扩展必须再利用另外的程序构造动态
sql:
select sum(case when 课程id = 'k1' then 成绩 else 0 end)/sum(case 课程id when 'k1' then 1 else 0 end) as 数学平均分
,100 * sum(case when 课程id = 'k1' and 成绩 >= 60 then 1 else 0 end)/sum(case when 课程id = 'k1' then 1 else 0 end) as 数学及格百分数
,sum(case when 课程id = 'k2' then 成绩 else 0 end)/sum(case 课程id when 'k2' then 1 else 0 end) as 语文平均分
,100 * sum(case when 课程id = 'k2' and 成绩 >= 60 then 1 else 0 end)/sum(case when 课程id = 'k2' then 1 else 0 end) as 语文及格百分数
,sum(case when 课程id = 'k3' then 成绩 else 0 end)/sum(case 课程id when 'k3' then 1 else 0 end) as 英语平均分
,100 * sum(case when 课程id = 'k3' and 成绩 >= 60 then 1 else 0 end)/sum(case when 课程id = 'k3' then 1 else 0 end) as 英语及格百分数
,sum(case when 课程id = 'k4' then 成绩 else 0 end)/sum(case 课程id when 'k4' then 1 else 0 end) as 政治平均分
,100 * sum(case when 课程id = 'k4' and 成绩 >= 60 then 1 else 0 end)/sum(case when 课程id = 'k4' then 1 else 0 end) as 政治及格百分数
from 成绩表 t
这一句看起来很长,但实际上是最经典的 case 运用,很实用的数据分析技术。先将原
表中的成绩一列连续投影 8 次备用于四门不同课程,充分利用 case 和数据的值域
['k1','k2','k3','k4']来划分数据,再利用 sum() [1 + ...+ 1] 实现了看似本来应
该用 count(*) 的计数器的功能,这里面不要说联接和子查询,甚至连 group by 分组
的痕迹都找不到!如果读起来吃力,完全可以先只保留一个字段,相对好理解些,看懂后
逐一补全。本题也可以算一个"行变列"的交叉透视表示例吧! 另外,"行"相对于"列"
是动态的,"行"是相对无限的,"列"是相对有限的,"行"的增删是应用级的,可"随意"增
删,"列"的增删是管理级的,不要轻易变动!
6.按不同老师所教不同课程平均分从高到低列印: (就是分析哪个老师的哪个课程水平高)
教师id,教师姓名,课程id,课程名称,平均分
select 教师id,max(教师姓名) as 教师姓名,课程id,max(课程名称) as 课程名称,avg(成绩) as 平均成绩
from 成绩表 t
group by 课程id,教师id
order by avg(成绩) desc
这道题的确没啥好说的,就算闭着眼,不动手,答案也应脱口而出!
如果平均分按去掉一个最高分和一个最低分后取得,则也不难写出:
select 教师id,max(教师姓名),课程id,max(课程名称) as 课程名称 --,avg(成绩) as 平均成绩
,(sum(成绩)
-(select max(成绩)
from 成绩表
where 课程id= t1.课程id and 教师id = t1.教师id)
-(select min(成绩)
from 成绩表
where 课程id= t1.课程id and 教师id = t1.教师id))
/ cast((select count(*) -2
from 成绩表
where 课程id= t1.课程id and 教师id = t1.教师id) as float) as 平均分
from 成绩表 as t1
where (select count(*) -2
from 成绩表
where 课程id = t1.课程id and 教师id = t1.教师id) >0
group by 课程id,教师id
order by 平均分 desc
********************************************************************************************
7.列印数学成绩第 10 名到第 15 名的学生成绩单
或列印平均成绩第 10 名到第 15 名的学生成绩单
[学生id],[学生姓名],数学,语文,英语,政治,平均成绩
如果只考虑一门课程,如:数学成绩,非常简单:
select top 5 *
from t
where 课程id ='k1'
and 成绩 not in(select top 15 成绩
from t
order by 成绩 desc
)
order by 成绩 desc
union
select *
from t
where 课程id ='k1'
and 成绩 not in(select top 10 成绩
from t
order by 成绩 desc
)
and 成绩 in(select top 15 成绩
from t
order by 成绩 desc
)
order by 成绩 desc
从逻辑上说,第 10 名到第 15 名就是从原前 15 名,"再"挑出前 5 名不要,保留剩下
的 5 名。第二种写法是从前 15 名里挑出不属于原前 10 名的记录,把两个数据集做
一个差,因此要多用一个
子查询,效率相对较低,它,如果要有《ansi/iso sql》的 except
关键字就是最理想的了。
这种技巧在数据"分页"的应用中经常利用,只要遵循如下原则即可:
select top @pagesize *
from t
where sortfield not in (select top @pagesize * @pagei sortfield
from t
order by sortfield
)
order by sortfield
至此,该题考察的主要目的已经达到。至于列印明晰成绩单:
[学生id],[学生姓名],数学,语文,英语,政治,平均成绩 前面也有类似的题目,做起来
确实麻烦,因此下面仅提供参考答案,就不赘述了:
select distinct top 5
[成绩表].[学生id],
[成绩表].[学生姓名] as 学生姓名,
[t1].[成绩] as 数学,
[t2].[成绩] as 语文,
[t3].[成绩] as 英语,
[t4].[成绩] as 政治,
isnull([t1].[成绩],0) + isnull([t2].[成绩],0) + isnull([t3].[成绩],0) + isnull([t4].[成绩],0) as 总分
from [成绩表]
left join [成绩表] as [t1]
on [成绩表].[学生id] = [t1].[学生id] and [t1].[课程id] = 'k1'
left join [成绩表] as [t2]
on [成绩表].[学生id] = [t2].[学生id] and [t2].[课程id] = 'k2'
left join [成绩表] as [t3]
on [成绩表].[学生id] = [t3].[学生id] and [t3].[课程id] = 'k3'
left join [成绩表] as [t4]
on [成绩表].[学生id] = [t4].[学生id] and [t4].[课程id] = 'k4'
where isnull([t1].[成绩],0) + isnull([t2].[成绩],0) + isnull([t3].[成绩],0) + isnull([t4].[成绩],0)
not in
(select
distinct
top 15 with ties
isnull([t1].[成绩],0) + isnull([t2].[成绩],0) + isnull([t3].[成绩],0) + isnull([t4].[成绩],0)
from [成绩表]
left join [成绩表] as [t1]
on [成绩表].[学生id] = [t1].[学生id] and [t1].[课程id] = 'k1'
left join [成绩表] as [t2]
on [成绩表].[学生id] = [t2].[学生id] and [t2].[课程id] = 'k2'
left join [成绩表] as [t3]
on [成绩表].[学生id] = [t3].[学生id] and [t3].[课程id] = 'k3'
left join [成绩表] as [t4]
on [成绩表].[学生id] = [t4].[学生id] and [t4].[课程id] = 'k4'
order by isnull([t1].[成绩],0) + isnull([t2].[成绩],0) + isnull([t3].[成绩],0) + isnull([t4].[成绩],0) desc)
最后还要多说一句: 一般 top 关键字与 order by 子句合用才有真正意义。
