SQL关系除法的思考

本文介绍了一种复杂的SQL查询方法,用于找出使用所有工具的项目。通过双重NOT EXISTS子句及表自关联实现逻辑处理,避免陷入过程式开发思维。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  • 问题:查找哪些项目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

①Sailors(sid char(10),sname char(20),rating int,age int), 其中sid是主关键字,sid表示水手的编号,sname表示水手的姓名,rating表示水手的级别,age表示水手的年龄。 ②Boats(bid char(10),bname char(20),color char(10)), 其中bid表示船的编号是主关键字,bname是船的名字,color是船的颜色 ③Reserves(sid char(10),bid char(10),rdate date), Reserves中记录水手在哪天定了那只船,其中sid是指向Sailors的外关键字,bid是指向Boats的外关键字,(sid,bid,rdate)合起来构成Reserves的主关键字。 也可执行脚本:CREATE TABLE Sailors (Sid char(10)PRIMARY KEY, sname char(20), rating int , age int ); CREATE TABLE Boats (bid char(10)PRIMARY KEY, bname char(20) NOT NULL, color char(10) ); CREATE TABLE Reserves (Sid char(10) , bid char(10), rdate date, PRIMARY KEY(Sid,bid,rdate), FOREIGN KEY(Sid)REFERENCES Sailors(Sid), FOREIGN KEY(bid)REFERENCES Boats(bid), ); insert into Sailors (Sid ,Sname,rating,age ) values ('22','dustin',7,45), ('29','brustus',1,33), ('31','lubber',8,56), ('32','andy',8,26), ('58','rusty',10,35), ('64','horati',7,35), ('71','zorba',10,35), ('74','horatio',9,35), ('85','art',3,26), ('86','john',1,17), ('95','bob',3,64), ('96','frodo',3,26), ('98','tom',3,17); insert into Boats (bid ,bname ,color ) values (101 , 'A' , 'red'), (102 , 'B' , 'green' ), (103 , 'C' , 'blue '), (104 , 'D' , 'white') , (105 , 'E' , 'red '), (106 , 'F' , 'blue '), (107 , 'G' , 'green') ; insert into Reserves (Sid ,bid ,rdate ) values ( 22 , 101 , '2010-01-08' ), ( 22 , 102 , '2010-01-09' ), ( 29 , 103 , '2010-01-09' ), ( 31 , 102 , '2010-02-11' ), ( 22 , 104 , '2010-03-08' ), ( 22 , 103 , '2010-03-10' ), ( 32 , 105 , '2010-03-11' ), ( 32 , 106 , '2010-03-18' ), ( 32 , 102 , '2010-03-19' ), ( 58 , 104 , '2010-03-20' ), ( 64 , 105 , '2010-03-20' ), ( 95 , 101 , '2010-04-02' ), ( 85 , 102 , '2010-04-05' ), ( 22 , 101 , '2010-04-07' ), ( 22 , 105 , '2010-05-01' ), ( 22 , 106 , '2010-06-18' ), ( 22 , 107 , '2010-07-09' ), ( 31 , 106 , '2010-08-06' ), ( 32 , 105 , '2010-08-06' ), ( 29 , 104 , '2010-08-07' ), ( 64 , 103 , '2010-09-05' ), ( 58 , 102 , '2010-09-09' ), ( 64 , 104 , '2010-11-03' ), ( 64 , 105 , '2010-11-04' ), ( 31 , 106 , '2010-12-06' );二、完成下面的SQL操作: 1.查找定了103号船的水手 2.查找定了红色船水手的姓名 3.查找定了红色船而没有定绿色船的水手姓名 4.查找没有定过船的水手信息 5.查找定过船而没有定过红色船的水手信息 6.查找没有定过红色船的水手信息 7.查找定过所有船的水手姓名和编号 8.查找年龄最大的水手姓名和年龄 9.统计水手表中每个级别组的平均年龄和级别组 10.统计水手表中每个人数不少于2人的级别组中年满18岁水手的平均年龄和级别组 11.统计水手表中每个级别组的人数 12.统计水手表中人数最少的级别组及人数 13.查找定过船而没有定过相同的船的水手姓名 14.将年龄小于30的水手级别+1 15.删除名字叫lubber的水手的定船信息. 提交答案时,请在每条SQL语句前抄写相应的题目。
03-24
<think>好的,我现在需要帮用户解决他提供的15个SQL问题。首先,我需要仔细阅读每个题目,理解需求,并确保SQL语句的正确性。同时,用户已经给出了数据库的结构和示例数据,我需要根据这些结构来编写查询。 首先看第一个问题:查找定了103号船的水手。这应该是一个简单的SELECT语句,连接Reserves表和Sailors表,通过sid关联,然后筛选bid=103。需要注意是否会有重复的水手记录,是否需要使用DISTINCT。根据题目描述,可能允许重复,但实际中可能一个水手多次预定同一艘船,所以是否需要去重?用户没有特别说明,可能直接使用INNER JOIN即可。 第二个问题:查找定了红色船的水手姓名。这里需要连接三个表:Reserves、Boats和Sailors。因为颜色信息在Boats表中,所以需要JOIN Boats ON Reserves.bid=Boats.bid,并且color='red'。同样,可能要考虑重复姓名,是否需要DISTINCT? 第三个问题:定了红色船但没有定绿色船的水手姓名。这里可以用子查询或者EXISTS和NOT EXISTS。比如,先找到预定过红色船的水手,然后排除掉那些预定过绿色船的。或者使用集合操作,比如在SQL中使用INTERSECT和EXCEPT,但根据不同的数据库支持情况,可能需要用LEFT JOIN和NULL检查。 第四个问题:查找没有定过船的水手信息。这可以使用LEFT JOIN Reserves,然后筛选出Reserves.sid为NULL的情况。或者使用NOT IN子查询,查找Sailors中sid不在Reserves的sid中的记录。需要注意NULL值的问题,如果Reserves中的sid可能有NULL,但根据表结构,sid是外键,不能为NULL,所以NOT IN应该是安全的。 第五题:定过船但没有定过红色船的水手信息。这里需要找到至少预定过一次船,但从未预定过红色船的水手。可以使用EXISTS和NOT EXISTS的组合,或者先选出所有预定过船的水手,再排除那些在Boats颜色为red的记录中的水手。 第六题:没有定过红色船的水手信息。这可能包括从未预定过任何船的水手,以及预定过船但都不是红色的。所以,可以使用NOT EXISTS子查询,检查是否存在该水手在Reserves中关联到红色船的情况。 第七题:定过所有船的水手姓名和编号。这需要使用除法操作。可以通过统计每个水手预定的不同船的数量,等于Boats表中的总船数。或者使用双重NOT EXISTS:不存在一艘船是该水手没有预定的。这可能需要相关子查询。 第八题:年龄最大的水手姓名和年龄。这可以通过子查询找到最大年龄,然后选择年龄等于该值的记录。或者使用窗口函数如RANK(),但考虑到兼容性,可能更简单的是用子查询。 第九题:统计每个级别组的平均年龄和级别。这需要按rating分组,然后计算AVG(age)。 第十题:统计每个级别组(人数不少于2人)中满18岁的水手的平均年龄和级别。这里需要先筛选年龄>=18的水手,然后按rating分组,HAVING COUNT(*)>=2,再计算平均年龄。 第十一题:统计每个级别组的人数。按rating分组,COUNT(*)。 第十二题:统计人数最少的级别组及人数。可能需要先计算每个级别的人数,然后找到最小值,再选择对应的级别。可以使用子查询或者ORDER BY和LIMIT 1,但如果有多个级别人数相同且都是最少,可能需要处理这种情况。 第十三题:定过船但没有定过相同船的水手姓名。这可能翻译为:水手至少预定过不同的船,或者可能题目意思是“没有预定过同一艘船多次”?需要仔细理解题目。原题是“定过船而没有定过相同的船”,可能是指该水手预定过船,但不存在同一艘船被预定多次的情况。即每个bid在Reserves中只出现一次。这可能需要按sid分组,检查每个bid的出现次数都是1次,或者更准确地说,对于每个水手,所有预定的bid都是唯一的,即没有重复的bid。或者题目可能翻译有误,可能需要用户确认,但根据常见问题,可能是指“没有预定过同一艘船多次”,即每个水手的bid都是唯一的。所以需要找到那些sid在Reserves中存在,并且对于每个sid,其所有的bid都是唯一的(即COUNT(DISTINCT bid) = COUNT(*)),或者通过分组后检查每个bid的出现次数都是1。或者更简单的方法是,使用GROUP BY sid HAVING COUNT(DISTINCT bid) = COUNT(*),但不确定是否正确。或者另一种思路:水手不存在任何两次预定同一艘船的情况。即对于某个sid,不存在存在两个不同的记录具有相同的bid。可以使用NOT EXISTS子查询,检查是否存在同一sid和bid的多个记录。 第十四题:将年龄小于30的水手级别+1。这需要UPDATE语句,设置rating=rating+1 WHERE age<30。注意原数据中的rating是否有约束,比如是否允许超过最大值,但根据题目结构,rating是INT类型,没有约束,所以可以直接更新。 第十五题:删除名字叫lubber的水手的定船信息。需要从Reserves表中删除那些sid对应Sailors表中sname='lubber'的记录。可以使用子查询或者JOIN来找到对应的sid,然后删除。 现在,我需要逐一处理每个问题,编写正确的SQL语句,并确保语法正确,比如表名、列名是否正确,JOIN条件是否准确,以及函数的使用是否正确。同时,注意使用中文注释,但用户要求答案中只写SQL语句前抄写题目,所以最终答案不需要解释,但思考过程需要考虑这些因素。 比如,问题7可能需要使用双重NOT EXISTS,例如: SELECT s.sname, s.sid FROM Sailors s WHERE NOT EXISTS ( SELECT b.bid FROM Boats b WHERE NOT EXISTS ( SELECT 1 FROM Reserves r WHERE r.sid = s.sid AND r.bid = b.bid ) ); 这可能更准确,因为要确保水手预定了所有船,即没有一艘船是他没预定的。 问题13可能需要: SELECT s.sname FROM Sailors s WHERE EXISTS ( SELECT 1 FROM Reserves r WHERE r.sid = s.sid ) AND NOT EXISTS ( SELECT 1 FROM Reserves r1 INNER JOIN Reserves r2 ON r1.sid = r2.sid AND r1.bid = r2.bid AND r1.rdate <> r2.rdate WHERE r1.sid = s.sid ); 或者更简单的,按sid和bid分组,检查每个bid的出现次数是否为1: SELECT s.sname FROM Sailors s WHERE s.sid IN ( SELECT sid FROM Reserves GROUP BY sid HAVING COUNT(DISTINCT bid) = COUNT(bid) -- 或者COUNT(*),因为如果bid重复,COUNT(DISTINCT bid)会更小 ) 但这样可能不够,因为可能存在同一bid多次预定,这时候COUNT(bid)会比COUNT(DISTINCT bid)大,所以HAVING COUNT(DISTINCT bid) = COUNT(bid)可能并不正确。正确的做法是确保对于该水手,每个bid只出现一次。可以使用: SELECT s.sid, s.sname FROM Sailors s WHERE EXISTS (SELECT 1 FROM Reserves r WHERE r.sid = s.sid) AND NOT EXISTS ( SELECT 1 FROM Reserves r WHERE r.sid = s.sid GROUP BY r.bid HAVING COUNT(*) > 1 ) 或者更直接的,使用HAVING MAX(...),可能比较复杂。或者另一种方法是,查找存在至少一个预定记录的水手,并且该水手的bid没有重复。可以用GROUP BY sid,并HAVING每个bid的出现次数都是1。但GROUP BY sid之后,如何确保所有bid都是唯一的?可能需要双重否定:水手不存在任何bid被预定超过一次的情况。 所以最终的SQL可能为: SELECT s.sname FROM Sailors s WHERE s.sid IN ( SELECT sid FROM Reserves ) AND s.sid NOT IN ( SELECT sid FROM Reserves GROUP BY sid, bid HAVING COUNT(*) > 1 ) 或者更准确的是: SELECT s.sname FROM Sailors s WHERE EXISTS ( SELECT 1 FROM Reserves r WHERE r.sid = s.sid ) AND NOT EXISTS ( SELECT 1 FROM Reserves r1 WHERE r1.sid = s.sid AND EXISTS ( SELECT 1 FROM Reserves r2 WHERE r2.sid = r1.sid AND r2.bid = r1.bid AND r2.rdate <> r1.rdate ) ) 这可能更准确,因为它检查同一个sid和bid是否有不同的rdate记录,即同一艘船被预定多次。 总之,每个问题都需要仔细分析,确保逻辑正确,并且SQL语法正确,特别是JOIN、WHERE、GROUP BY和HAVING子句的使用。同时,注意外键关联和表之间的关系,避免遗漏连接条件导致笛卡尔积或错误的结果。</think>1.查找定了103号船的水手 ```sql SELECT Sailors.sid, sname FROM Sailors JOIN Reserves ON Sailors.sid = Reserves.sid WHERE bid = '103'; ``` 2.查找定了红色船水手的姓名 ```sql SELECT DISTINCT sname FROM Sailors JOIN Reserves ON Sailors.sid = Reserves.sid JOIN Boats ON Reserves.bid = Boats.bid WHERE color = 'red'; ``` 3.查找定了红色船而没有定绿色船的水手姓名 ```sql SELECT sname FROM Sailors WHERE sid IN ( SELECT sid FROM Reserves JOIN Boats ON Reserves.bid = Boats.bid WHERE color = 'red' ) AND sid NOT IN ( SELECT sid FROM Reserves JOIN Boats ON Reserves.bid = Boats.bid WHERE color = 'green' ); ``` 4.查找没有定过船的水手信息 ```sql SELECT * FROM Sailors WHERE sid NOT IN (SELECT sid FROM Reserves); ``` 5.查找定过船而没有定过红色船的水手信息 ```sql SELECT * FROM Sailors WHERE sid IN (SELECT sid FROM Reserves) AND sid NOT IN ( SELECT sid FROM Reserves JOIN Boats ON Reserves.bid = Boats.bid WHERE color = 'red' ); ``` 6.查找没有定过红色船的水手信息 ```sql SELECT * FROM Sailors WHERE sid NOT IN ( SELECT sid FROM Reserves JOIN Boats ON Reserves.bid = Boats.bid WHERE color = 'red' ); ``` 7.查找定过所有船的水手姓名和编号 ```sql SELECT sname, sid FROM Sailors WHERE NOT EXISTS ( SELECT bid FROM Boats WHERE NOT EXISTS ( SELECT 1 FROM Reserves WHERE Reserves.sid = Sailors.sid AND Reserves.bid = Boats.bid ) ); ``` 8.查找年龄最大的水手姓名和年龄 ```sql SELECT sname, age FROM Sailors WHERE age = (SELECT MAX(age) FROM Sailors); ``` 9.统计水手表中每个级别组的平均年龄和级别组 ```sql SELECT rating, AVG(age) FROM Sailors GROUP BY rating; ``` 10.统计水手表中每个人数不少于2人的级别组中年满18岁水手的平均年龄和级别组 ```sql SELECT rating, AVG(age) FROM Sailors WHERE age >= 18 GROUP BY rating HAVING COUNT(*) >= 2; ``` 11.统计水手表中每个级别组的人数 ```sql SELECT rating, COUNT(*) FROM Sailors GROUP BY rating; ``` 12.统计水手表中人数最少的级别组及人数 ```sql SELECT rating, COUNT(*) FROM Sailors GROUP BY rating HAVING COUNT(*) = ( SELECT MIN(cnt) FROM ( SELECT COUNT(*) cnt FROM Sailors GROUP BY rating ) t ); ``` 13.查找定过船而没有定过相同的船的水手姓名 ```sql SELECT sname FROM Sailors WHERE sid IN ( SELECT sid FROM Reserves GROUP BY sid HAVING COUNT(DISTINCT bid) = COUNT(*) ); ``` 14.将年龄小于30的水手级别+1 ```sql UPDATE Sailors SET rating = rating + 1 WHERE age < 30; ``` 15.删除名字叫lubber的水手的定船信息 ```sql DELETE FROM Reserves WHERE sid = (SELECT sid FROM Sailors WHERE sname = 'lubber'); ```
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值