|
看完测试完下面这些试题，你的ＳＱＬ水平一定会有新的提高。
下面我们先看一下题设:
二维表 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
这样,极大地减小了在线数据被无意破坏的可能性,当然数据提前备份也很重要。同理
[1] [2] [3] [4] 下一页 |
