实例分析SQL中除法的三种写法

首先请看

http://topic.csdn.net/u/20070821/15/8a2dac05-51b8-4eb1-b41d-4fbe8a232c85.html

帖中从实际问题出发，讨论SQL语句中如何表示除法运算。

 

以下是问题的给出与简要描述：

 

view plain copy to clipboard print ?

1. 表A：商店信息   
2. ANO,   ANAME,   WQTY,   CITY   
3. 101,   韶山书店,   15,   长沙   
4. 204,   前门商店,   89,   北京   
5. 256,   东风商场,   501,   北京   
6. 345,   铁道商店,   76,   长沙   
7. 620,   武汉商场,   413,   武汉   
8.   
9. 表B：商品信息   
10. BNO,   BNAME,   PRICE   
11. 1,   毛笔,   21   
12. 2,   羽毛球,   4   
13. 3,   收音机,   325   
14. 4,   书包,   242   
15.   
16. 表AB：商店-商品对应关系   
17. ANO,   BNO,   QTY   
18. 101,   1,   105   
19. 101,   2,   42   
20. 101,   3,   25   
21. 101,   4,   104   
22. 204,   3,   61   
23. 256,   1,   241   
24. 256,   2,   91   
25. 345,   1,   141   
26. 345,   2,   18   
27. 345,   4,   74   
28. 620,   4,   125   
29.   
30.    
31.   
32. 现在要找出至少供应代号为256的商店所供应的全部商品的商店代号ANO，只涉及到表AB。这个SQL语句该怎么写？   
33.   
34. 具体说，我已经知道关系表达式是：   
35. π   ano,   bno   (AB)   ÷   π   bno   (σ   ano   =   256   (AB));   
36.   
37. 我的问题是：如何把这里的除法运算转化为SQL语句呢？  

    

 

 

然后下面有很多的回答，在这里我就不一一列出了。

先给出我的测试DDL与SQL语句。

 

 

view plain copy to clipboard print ?

1. --DDL:  
2.   
3. CREATE TABLE AB(  
4.     ANO INT,  
5.     BNO INT,  
6.     QTY INT  
7. );  
8.   
9. INSERT INTO AB VALUES ( 101,   1,   105 );  
10. INSERT INTO AB VALUES ( 101,   2,   42 );  
11. INSERT INTO AB VALUES ( 101,   3,   25 );  
12. INSERT INTO AB VALUES ( 101,   4,   104 );  
13. INSERT INTO AB VALUES ( 204,   3,   61 );  
14. INSERT INTO AB VALUES ( 256,   1,   241 );  
15. INSERT INTO AB VALUES ( 256,   2,   91 );  
16. INSERT INTO AB VALUES ( 345,   1,   141 );  
17. INSERT INTO AB VALUES ( 345,   2,   18 );  
18. INSERT INTO AB VALUES ( 345,   4,   74 );  
19. INSERT INTO AB VALUES ( 620,   4,   125 );  
20.   
21.   
22. --SQL:  
23.   
24. --1、正统做法：  
25.   
26. SELECT DISTINCT ANO FROM AB  
27. WHERE ANO NOT IN (  
28.     SELECT ANO FROM (  
29.         SELECT ANO,BNO FROM (  
30.             SELECT ANO FROM AB  
31.         ) AS r1 CROSS JOIN (  
32.             SELECT BNO FROM AB  
33.             WHERE ANO = 256  
34.         ) AS s1  
35.     ) AS rs1  
36.     WHERE CHECKSUM(ANO,BNO) NOT IN (  
37.         SELECT CHECKSUM(ANO,BNO) FROM AB  
38.     )  
39. );  
40.   
41.    
42.   
43. --2、我的一位同学想出来的解法，想法非常好：  
44.   
45. SELECT ANO FROM (  
46.     SELECT ANO FROM AB  
47.     WHERE BNO IN (  
48.         SELECT BNO FROM AB  
49.         WHERE ANO = 256  
50.     )  
51. ) AS rs1  
52. GROUP BY ANO  
53. HAVING COUNT(ANO) = (  
54.     SELECT COUNT(*) FROM AB  
55.     WHERE ANO = 256  
56. );  
57.   
58. --3、第二天早上想到的方法，就是传说中的双NOT EXISTS方法，应该是结构最直观的方法了。  
59. SELECT DISTINCT ANO FROM AB AS AB1  
60. WHERE NOT EXISTS (  
61.     SELECT 1 FROM AB AS AB2  
62.     WHERE ANO = 256  
63.     AND NOT EXISTS (  
64.         SELECT 1 FROM AB AS AB3  
65.         WHERE AB3.BNO = AB2.BNO  
66.         AND AB3.ANO = AB1.ANO  
67.     )  
68. );  

   

 

 

解法一：

解法一使用的是《数据库系统概念(第五版)》上给出的正统转换方法：

r ¸ s = { t  |  t Î Õ R-S(r) Ù " u Î s ( tu Î r ) }

r ¸ s = ÕR-S (r) –ÕR-S ( (ÕR-S (r) x s) – ÕR-S,S(r))

 

我在这里做的就是把这段关系表达式转换成SQL语句就可以了。

在这里稍微解释一下这个关系表达式的由来。

 

整体思路就是先选出r集合中的(R-S)*(1)属性，然后与s集合进行笛卡尔操作，这时候得到的是(ÕR-S (r) x s) ，就是r集合中全部元组都能被选出来的理想情况*(2)（姑且这么说）；然后减去集合r中的现实情况*(3)ÕR-S,S(r))（表示集合r属性的重新排列：而属性重新排列是集合进行差集运算所要求的），得到现实与理想的“差距”，并选出(R-S)的属性，就得到ÕR-S ( (ÕR-S (r) x s) – ÕR-S,S(r))。最后再用ÕR-S (r)（实际的集合）来与刚刚得出的ÕR-S ( (ÕR-S (r) x s) – ÕR-S,S(r))进行差集运算，得出符合理想（规定）的那些元组——而这些元组就是r ¸ s的结果了。

 

注：(1) (R-S)属性指的是r集合与s集合的关系模式的差集（即r集合中的字段集合与s集合中的字段集合的差集），其中S  Í R，这是集
            合除法规定的。

       (2) 理想情况指的是所有元组都能成功配对的情况（通过笛卡尔积实现）。

       (3) 现实情况指的是当前r集合中的实际元组情况。

 

 

 

解法二： 

解法二的思路十分地独特，它巧妙地运用group by而大大简化了操作。

整体思路就是①先把r集合中的与s集合的相同字段（模式S）有相同值的元组提取出来（使用IN操作就可以了），②然后再对查询结果以它们相异的字段（模式R-S）分组，然后统计每个分组的COUNT是否等于期望的COUNT值（即s集合中的元组数），符合的就是结果了。

思路就这么简单，大家仔细想想就明白了。

 

 

解法三：

 解法三就是传说中的双NOT EXISTS做法了，它的思路非常的直观，就是三层SELECT语句的嵌套。

 

整体思路就是，先求出ANO为256的BNO集合，然后与针对每一个相同ANO的BNO集合进行差集运算，如果结果为空集，那么就证明了该ANO的BNO集合包含了预期的BNO集合（即ANO为256的BNO集合），该集合可被输出，然后继续下一个ANO。

Note that X – Y = Ø   Û   X Í Y

 

其实就是以上这条公式的思路。

 

 

 

 

讲解中可能有不正确的地方，也希望大家可以指出~ ^_^

如果大家还有更多的好的写法，也欢迎大家留下~