我有如下裝運表。
選擇 ShipperNo,Parts from SHIPMENT
| 發貨人編號 | 部分 |
|---|---|
| S1 | P1 |
| S1 | P2 |
| S1 | P3 |
| S1 | P4 |
| S2 | P1 |
| S2 | P2 |
| S3 | P1 |
| S4 | P2 |
| S4 | P3 |
我需要找到所有運送 S2運送的所有零件的托運人。
從 SHIPMENT 中選擇零件，其中 ShipperNo='S2'
將是
| 部分 |
|---|
| P1 |
| P2 |
我不確定如何在上述兩個子查詢之間進行交集并獲得 ShipperNo。我不一定是每天都使用 SQL 的 DBA，這個問題在面試中難倒了我，我更像是一個具有基本 SQL 知識的全堆疊開發人員。
Edit1：我忘了提到面試官要求我在不使用count 的情況下執行此操作。
uj5u.com熱心網友回復：
為每個托運人計算其與 S2 共同擁有的零件數量，然后過濾與 S2 具有完全相同零件數量的零件：
select SH1.ShipperNo, count(*) partsCount
from shipment SH1
where SH1.Parts in (select SH2.Parts from SHIPMENT SH2 WHERE SH2.ShipperNo = 'S2')
group by SH1.ShipperNo
having count(*) = (select count(*) from SHIPMENT SH3 WHERE SH3.ShipperNo = 'S2')
uj5u.com熱心網友回復：
您可以將 self-join與子查詢一起使用：
編輯：沒有count功能的解決方案，根據問題中的詳細資訊：
with sub_s2(id, c) as (
select s.shipperno, sum(s.shipperno != s1.shipperno and s1.shipperno = 'S2') from shipment s
join shipment s1 on s.parts = s1.parts
group by s.shipperno
)
select s.id shipperno from sub_s2 s where s.c = (select sum(s3.shipperno = 'S2') from shipment s3)
輸出：
shipperno
---------
S1
uj5u.com熱心網友回復：
COUNT不能使用，我想GROUP BY是允許的。我們可以使用GROUP_CONCAT來查找不同的部件串列并進行字串比較。
SELECT ShipperNo
FROM SHIPMENT
WHERE ShipperNo != 'S2'
GROUP BY ShipperNo
HAVING GROUP_CONCAT(DISTINCT Parts ORDER BY Parts ASC) LIKE (
SELECT CONCAT('%', GROUP_CONCAT(DISTINCT Parts ORDER BY Parts ASC), '%')
FROM SHIPMENT
WHERE ShipperNo = 'S2'
GROUP BY ShipperNo
)
如果GROUP BY不允許，使用EXISTS檢查
SELECT DISTINCT ShipperNo
FROM SHIPMENT a
WHERE ShipperNo != 'S2'
AND EXISTS (
SELECT 1
FROM SHIPMENT b
WHERE b.ShipperNo = 'S2'
AND b.Parts = a.Parts
)
AND NOT EXISTS (
SELECT 1
FROM SHIPMENT b
WHERE b.ShipperNo = 'S2'
AND NOT EXISTS (
SELECT 1
FROM SHIPMENT c
WHERE c.ShipperNo = a.ShipperNo
AND b.Parts = c.Parts
)
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/330922.html
上一篇：MySQL轉儲檔案為空