Question

我有两张这样的表格:

Supplier

  SN         SNAME  Stat   City
+----------+-------+----+--------+
| S1       | Smith | 20 | London |
| S2       | Jones | 10 | Paris  |
| S3       | Blake | 30 | Paris  |
| S4       | Clark | 20 | London |
| S5       | Adams | 30 | Athens |
+----------+-------+----+--------+

Shipment

 SN    PN   QTY
+----+----+-----+
| S1 | P1 | 300 |
| S1 | P2 | 200 |
| S1 | P3 | 400 |
| S1 | P4 | 200 |
| S1 | P5 | 100 |
| S1 | P6 | 100 |
| S2 | P1 | 300 |
| S2 | P2 | 400 |
| S3 | P2 | 200 |
| S4 | P2 | 200 |
| S4 | P4 | 300 |
| S4 | P5 | 400 |
+----+----+-----+

I have the find the following:

列出供应商S2提供的至少所有部分(PN)的供应商名单。

换言之,我需要列出所有至少装运P1和P2的供应商,尽管我显然需要把更多的注意力集中在眼前的问题上。

我很相信,我必须使用某种形式的NOT EXISTS——也许不存在双重——来做到这一点。我试图自行参加船舶表格——但我无法理解,如何使其超越了在尼泊尔共产党S2号案中提出的“纽约”项目的检查,而是在名单上核实S2的所有部件,然后列入名称。

Answer 1

我用一个共同的表格来表明这一点:

--CTE will contain records from the Supplier you are looking to check against
WITH desiredSN AS
(
   SELECT s.SN, s.PN 
   FROM Shipment AS s
   WHERE s.SN =  S2 
)
--SELECT will join all other shippers with each record of your Supplier S2 here
SELECT s.SN
FROM Shipment AS s
INNER JOIN desiredSN AS d ON s.PN = d.PN
WHERE s.SN !=  S2 
GROUP BY s.SN
--Having ensures that the count of your old supplier matches
--the number a potential supplier overlaps with
HAVING COUNT(1) =
(
   SELECT COUNT(1)
   FROM desiredSN AS d
)

如果你不希望利用国家竞争性考试,你可以使用分局:

SELECT s.SN
FROM Shipment AS s
INNER JOIN 
(
   SELECT s.SN, s.PN
   FROM Shipment AS s
   WHERE s.SN =  S2 
)
AS d ON s.PN = d.PN
WHERE s.SN !=  S2 
GROUP BY s.SN
HAVING COUNT(1) =
(
   SELECT COUNT(1)
   FROM Shipment AS s
   WHERE s.SN =  S2 
)

Answer 2

您能否不使用一份特选声明来完善你们的成果?

例如:

SELECT SNAME FROM Supplier
INNER JOIN Shipment ON Supplier.SN = Shipment.SN
WHERE Shipment.PN IN (SELECT PN FROM Shipment WHERE SN = S2)

这样,部分(PN)仅限于S2出售的部分。

声明改为

Shipment.PN IN (SELECT PN FROM Shipment WHERE SN = S2)

现在,即使他们出售了其中一件物品,他们只有在出售至少<>>S2出售的物品时才能返回。

Supplier

Shipment

友情链接