SQL关系除法的思考

• 问题：查找哪些项目project正在使用工具站Tool的所有工具。

 

SELECT DISTINCT projectid FROM project P1
WHERE NOT  EXISTS
  (SELECT * FROM TOOL T WHERE NOT EXISTS
       (SELECT * FROM project P2 WHERE P2.toolid = T.toolid AND P1.projectid = P2.projectid )
  )

 

注意：上面的两个NOT  EXISTS的原理不一样！第一个NOT  EXISTS 表示判断之后有没有记录。第二个NOT  EXISTS 由于有(WHERE T1=T2)的语句，实际效果是判断TOOL的记录行是否存在project P2里面？

 

等效于：

SELECT DISTINCT projectid FROM project P1
WHERE NOT  EXISTS
  (SELECT * FROM TOOL T WHERE T.toolid NOT in
       (SELECT P2.toolid FROM project P2 WHERE P2.toolid = T.toolid AND P1.projectid = P2.projectid )
  )

 

需要特别理解：

 

SELECT * FROM TOOL T WHERE NOT EXISTS
       (SELECT * FROM project P2 WHERE P2.toolid = T.toolid AND P1.projectid = P2.projectid )
与

 

SELECT * FROM TOOL T WHERE T.toolid NOT in
       (SELECT P2.toolid FROM project P2 WHERE P2.toolid = T.toolid AND P1.projectid = P2.projectid )

 

的查询效果一样。

 

分析思路：

当分析的SQL比较复杂时，我们会先指定某具体的行进行思考SQL的设计， 如 projectid = 1 ， projectid = 2 ... 。 当我们确定了某个行的处理逻辑，需要提升到表集合操作时， 只要进行自身表关联，这有点类似实现了过程语句的循环处理功能。

 

也就是说，当我们需要对某个SQL语音进行循环处理时，就应考虑T AS T1 inner join T AS T2, 即记录表自关联。

 

例如由下面两个语句，

 

SELECT DISTINCT projectid FROM project P1
WHERE projectid = 1 and NOT  EXISTS
  (SELECT * FROM TOOL T WHERE NOT EXISTS
       (SELECT * FROM project P2 WHERE P2.toolid = T.toolid AND projectid = 1 )
  )   
 
   SELECT DISTINCT projectid FROM project P1
WHERE projectid = 2 and NOT  EXISTS
  (SELECT * FROM TOOL T WHERE NOT EXISTS
       (SELECT * FROM project P2 WHERE P2.toolid = T.toolid AND projectid = 2 )
  )

 

转为集合方式处理

SELECT DISTINCT projectid FROM project P1
WHERE  NOT  EXISTS
  (SELECT * FROM TOOL T WHERE NOT EXISTS
       (SELECT * FROM project P2 WHERE P2.toolid = T.toolid AND P1.projectid = P2.projectid )
  )

 

总结，

 

SQL 是逻辑描述性集合语言。要避免陷入过程式开发思维的方式，短短的几句SQL语句，可以实现C#语言复杂的循环代码处理。而且SQL语句看起来很像逻辑描述需要什么样的数据， 例如上例的SQL的大意是：

 

第一步：未被某项目使用的工具。

第二步：如果某个项目不存在未被使用的工具，就是说明这个项目使用了所有工具。

 

尽量用集合的方式思考，集合的思考特点是，常常考虑存在什么，不存在什么。

 

 

 

 关系除法一个较复杂的应用：

 

CREATE table #BundleSku (bundleSku VARCHAR(20),sku VARCHAR(20), Qty int)
CREATE TABLE #Ship (OrderItemId INT, bundleSku VARCHAR(20), sku VARCHAR(20), qty INT, scale int)

 

INSERT #BundleSku (bundleSku, sku, Qty) VALUES('b','s1',1)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('b','s2',2)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('c','c1',1)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('c','c2',2)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('c','c3',2)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('d','d1',1)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('d','d2',2)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('e','s1',1)
INSERT #BundleSku (bundleSku, sku, Qty) VALUES('e','s2',2)

 

INSERT #Ship (OrderItemId, bundleSku, sku, Qty, scale) VALUES(1,'b', 's1', 1, 1)
INSERT #Ship (OrderItemId, bundleSku, sku, Qty, scale) VALUES(1,'b', 's2', 3, 2)
INSERT #Ship (OrderItemId, bundleSku, sku, Qty, scale) VALUES(2,'c', 'c1', 1, 1)
INSERT #Ship (OrderItemId, bundleSku, sku, Qty, scale) VALUES(2,'c', 'c2', 1, 2)
INSERT #Ship (OrderItemId, bundleSku, sku, Qty, scale) VALUES(3,'b', 's1', 1, 1)
INSERT #Ship (OrderItemId, bundleSku, sku, Qty, scale) VALUES(3,'b', 's2', 3, 2)

 

SELECT * FROM #BundleSku

 

SELECT *, Qty/scale FROM #Ship

 

SELECT OrderItemId, shipQty= MIN(Qty/scale) FROM #Ship P1
WHERE NOT  EXISTS
  (SELECT * FROM #BundleSku T WHERE P1.bundleSku = T.bundleSku and  NOT EXISTS
       (SELECT * FROM #Ship P2 WHERE P2.sku = T.sku AND P1.OrderItemId = P2.OrderItemId )
  )
GROUP BY OrderItemId