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

最新推荐文章于 2023-05-19 22:27:45 发布

zhangsuiwen

最新推荐文章于 2023-05-19 22:27:45 发布

阅读量1.3w

点赞数 6

分类专栏：数据库文章标签： sql insert 数据库 join table 测试

本文链接：https://blog.csdn.net/zhangsuiwen/article/details/5087255

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

首先请看

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

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

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

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

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

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

--DDL: CREATE TABLE AB( ANO INT, BNO INT, QTY INT ); INSERT INTO AB VALUES ( 101, 1, 105 ); INSERT INTO AB VALUES ( 101, 2, 42 ); INSERT INTO AB VALUES ( 101, 3, 25 ); INSERT INTO AB VALUES ( 101, 4, 104 ); INSERT INTO AB VALUES ( 204, 3, 61 ); INSERT INTO AB VALUES ( 256, 1, 241 ); INSERT INTO AB VALUES ( 256, 2, 91 ); INSERT INTO AB VALUES ( 345, 1, 141 ); INSERT INTO AB VALUES ( 345, 2, 18 ); INSERT INTO AB VALUES ( 345, 4, 74 ); INSERT INTO AB VALUES ( 620, 4, 125 ); --SQL: --1、正统做法： SELECT DISTINCT ANO FROM AB WHERE ANO NOT IN ( SELECT ANO FROM ( SELECT ANO,BNO FROM ( SELECT ANO FROM AB ) AS r1 CROSS JOIN ( SELECT BNO FROM AB WHERE ANO = 256 ) AS s1 ) AS rs1 WHERE CHECKSUM(ANO,BNO) NOT IN ( SELECT CHECKSUM(ANO,BNO) FROM AB ) ); --2、我的一位同学想出来的解法，想法非常好： SELECT ANO FROM ( SELECT ANO FROM AB WHERE BNO IN ( SELECT BNO FROM AB WHERE ANO = 256 ) ) AS rs1 GROUP BY ANO HAVING COUNT(ANO) = ( SELECT COUNT(*) FROM AB WHERE ANO = 256 ); --3、第二天早上想到的方法，就是传说中的双NOT EXISTS方法，应该是结构最直观的方法了。 SELECT DISTINCT ANO FROM AB AS AB1 WHERE NOT EXISTS ( SELECT 1 FROM AB AS AB2 WHERE ANO = 256 AND NOT EXISTS ( SELECT 1 FROM AB AS AB3 WHERE AB3.BNO = AB2.BNO AND AB3.ANO = AB1.ANO ) );

解法一：

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

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的结果了。