(不考虑用缓冲表缓存不重复数据,删除元表,再反插数据回来) 《一道褒贬不一的 SQL 考试题》 http://www.triaton.com.cn/cgi-bin/lb5k/topic.cgi?forum=4&topic=97&show=0 1.如果 T 表还有一字段 F 数据类型为自动增量整型(唯一,不会重复), 而且 T 表中含有除 F 字段外,请删除其它字段完全相同的重复多余的脏记录数据: 这样的问题,等于我们要比较的是"学生ID"和"课程ID"两个字段的值,事实上,如果我们将它推广到 F0 外的 所有字段,那就是一个清理"逻辑重复"的记录问题,此时,有细心的读者将会觉得这种情况完全可以在设计基 本表时利用主键约束来杜绝!当然最好这样了!然而,现实情况经常是要把采集的原始数据洗洗干净才可以安 全使用,而且在采集原始数据时,往往不能用逻辑主键加以约束,例如: 在从刷卡机上采集考勤记录或是应用 程序操作日志等,否则将会带来不便,然而到了分析数据的时候,这些肮脏的数据就该扫地出门了! 之所以题 目中要保留这个自动标识列,是因为在以下题解中,自动标识列能起到非常重要的作用,请看: 方法一 DELETE L FROM "成绩表" L JOIN "成绩表" R ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F > R.F 这是思路最巧的,事实上,也是最直接有效的方法之一。很清晰明白。我们想要做的是重复数据只留一 条,删除其它。用一个不等自联接,正好可以将同一组重复数据中 F 字段值最小的那一条留下,选出其它的。 如果只有一条,当然是不会被选中了。很不错吧?! 这里要强调的是,大家一定要明确被操作的基本表也就是 DELETE 关键字后的表和过滤条件所使用的二维表,也就是 FROM 关键字子句的全部,如果还不够用 WHERE 子句就该出场了!一般来说,在生成过滤条件的 FROM 子句为表起别名,然后在整个语句中统一使用不但对于 程序员来说读写方便,用程序来构造动态 SQL 时,也很方便。如果上面的例子还不够直观,我们模仿一个不 等自联接,有一组数 (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 ) 这种方法思路很简单,就像翻译自然语言,很精确的描述了符合条件记录的特性。再进一步分析,发现两个过 滤 条件几乎没有什么差别,NOT IN 的效率问题且不说,这种重复代码绝对是编程中的大忌,我们应当用一个 >= 号在已经实现应用的基础上,为了提高效率来合并这两个条件: DELETE T FROM 成绩表 T WHERE 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 子句,其实也是没有必要的,去掉它应该会提高一些效率的。相比联接版本,内存占用可能小一 点,I/O 操作和 CPU 占用会更多一些。关于 DELETE 语句的调试,介绍一个经验。就是在确定过滤条件完全 正确之前,千万不要轻易的执行删除操作。这可不是 SELECT,错了再来一回。错删了数据再想找回来可是很 麻烦的。正确的调试方法是用无害的 SELECT 暂时代替危险的 DELETE。其实很简单,就是把 DELETE [FROM T] FROM ... 替换成 SELECT T.* FROM ...。 例如第一种方法,调试中应该先写为: SELECT L.* --DELECT L 暂时注释掉 FROM "成绩表" L JOIN "成绩表" R ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F>R.F 待到确定选出的结果集的确是想要删除的数据,再把写成 DELETE 的形式。这样,在最大程度上,保证了在线 数据的安全,当然重要数据提前备份也是很重要的,不过,如果数据在即时变动,这一招总不如保证不错删数据 来的更好些。同理 UPDATE 和 INSERT 操作也应照此行事。从数据库原理的关系运算的角度来看 INSERT、 UPDATE 和 INSERT 这些写操作同 SELECT ... FROM ... WHERE 一样,可以说都是典型的"选择(Selection)" 运算,UPDATE 和 INSERT 还是"投影(Projection)"运算,它们都是这些关系运算的"非只读"表现形式。其实 查询的目的也本来无非就是浏览、删除、更新或插入。 对于"子查询"和"连接"的使用,在效率是有差别的,但最关键的差别还是在查询的结果集的读写性上,开发人 员在写一个"只读"应用的查询,"子查询"和"连接"各自的效率就是应该首先考虑的问题,但是如果要实现"可 写"应用的查询,则无论是相关还是非相关子查询都是在复杂应用中难以避免的。 以上解决方案中,应该说第一种方法,简洁有效,很有创意,是值得推荐的方法。当然,最简单的写法应该是: DELETE T FROM T,T T1 WHERE T.学生ID=T1.学生ID and T.课程ID=T1.课程ID and T.F < T1.F 其实这就是方法一的标准连接写法,顺便说一下,以下各题答案为了便于读者理解,一般不采用标准连接的 写法,有兴趣的读者可以自己改写,如果使用 "*=" 实现外连接时,要注意此时 WHERE 子句的 AND 条件是 有顺序的。
《一道褒贬不一的 SQL 考试题》
http://www.triaton.com.cn/cgi-bin/lb5k/topic.cgi?forum=4&topic=97&show=0
1.如果 T 表还有一字段 F 数据类型为自动增量整型(唯一,不会重复),
而且 T 表中含有除 F 字段外,请删除其它字段完全相同的重复多余的脏记录数据: 这样的问题,等于我们要比较的是"学生ID"和"课程ID"两个字段的值,事实上,如果我们将它推广到 F0 外的
所有字段,那就是一个清理"逻辑重复"的记录问题,此时,有细心的读者将会觉得这种情况完全可以在设计基
本表时利用主键约束来杜绝!当然最好这样了!然而,现实情况经常是要把采集的原始数据洗洗干净才可以安
全使用,而且在采集原始数据时,往往不能用逻辑主键加以约束,例如: 在从刷卡机上采集考勤记录或是应用
程序操作日志等,否则将会带来不便,然而到了分析数据的时候,这些肮脏的数据就该扫地出门了! 之所以题
目中要保留这个自动标识列,是因为在以下题解中,自动标识列能起到非常重要的作用,请看:
方法一
DELETE L
FROM "成绩表" L
JOIN "成绩表" R
ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F > R.F 这是思路最巧的,事实上,也是最直接有效的方法之一。很清晰明白。我们想要做的是重复数据只留一
条,删除其它。用一个不等自联接,正好可以将同一组重复数据中 F 字段值最小的那一条留下,选出其它的。
如果只有一条,当然是不会被选中了。很不错吧?! 这里要强调的是,大家一定要明确被操作的基本表也就是
DELETE 关键字后的表和过滤条件所使用的二维表,也就是 FROM 关键字子句的全部,如果还不够用 WHERE
子句就该出场了!一般来说,在生成过滤条件的 FROM 子句为表起别名,然后在整个语句中统一使用不但对于
程序员来说读写方便,用程序来构造动态 SQL 时,也很方便。如果上面的例子还不够直观,我们模仿一个不
等自联接,有一组数 (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
) 这种方法思路很简单,就像翻译自然语言,很精确的描述了符合条件记录的特性。再进一步分析,发现两个过
滤 条件几乎没有什么差别,NOT IN 的效率问题且不说,这种重复代码绝对是编程中的大忌,我们应当用一个
>= 号在已经实现应用的基础上,为了提高效率来合并这两个条件: DELETE T
FROM 成绩表 T
WHERE 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 子句,其实也是没有必要的,去掉它应该会提高一些效率的。相比联接版本,内存占用可能小一
点,I/O 操作和 CPU 占用会更多一些。关于 DELETE 语句的调试,介绍一个经验。就是在确定过滤条件完全
正确之前,千万不要轻易的执行删除操作。这可不是 SELECT,错了再来一回。错删了数据再想找回来可是很
麻烦的。正确的调试方法是用无害的 SELECT 暂时代替危险的 DELETE。其实很简单,就是把
DELETE [FROM T] FROM ... 替换成 SELECT T.* FROM ...。
例如第一种方法,调试中应该先写为: SELECT L.*
--DELECT L 暂时注释掉
FROM "成绩表" L
JOIN "成绩表" R
ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F>R.F 待到确定选出的结果集的确是想要删除的数据,再把写成 DELETE 的形式。这样,在最大程度上,保证了在线
数据的安全,当然重要数据提前备份也是很重要的,不过,如果数据在即时变动,这一招总不如保证不错删数据
来的更好些。同理 UPDATE 和 INSERT 操作也应照此行事。从数据库原理的关系运算的角度来看 INSERT、
UPDATE 和 INSERT 这些写操作同 SELECT ... FROM ... WHERE 一样,可以说都是典型的"选择(Selection)"
运算,UPDATE 和 INSERT 还是"投影(Projection)"运算,它们都是这些关系运算的"非只读"表现形式。其实
查询的目的也本来无非就是浏览、删除、更新或插入。 对于"子查询"和"连接"的使用,在效率是有差别的,但最关键的差别还是在查询的结果集的读写性上,开发人
员在写一个"只读"应用的查询,"子查询"和"连接"各自的效率就是应该首先考虑的问题,但是如果要实现"可
写"应用的查询,则无论是相关还是非相关子查询都是在复杂应用中难以避免的。 以上解决方案中,应该说第一种方法,简洁有效,很有创意,是值得推荐的方法。当然,最简单的写法应该是: DELETE T
FROM T,T T1
WHERE T.学生ID=T1.学生ID and T.课程ID=T1.课程ID and T.F < T1.F 其实这就是方法一的标准连接写法,顺便说一下,以下各题答案为了便于读者理解,一般不采用标准连接的
写法,有兴趣的读者可以自己改写,如果使用 "*=" 实现外连接时,要注意此时 WHERE 子句的 AND 条件是
有顺序的。