获取支持SQL中所有传递模式的产品

Question

我有一张桌子ProductDeliveryModes，作为：

ProductId    DeliveryId
P101         D1
P101         D2
P101         D3
P102         D1
P102         D2
P102         D3
P103         D1

我需要得到支持所有交付模式(D1，D2，D3)的产品。从表中看，产品应该是: P101和P102。

我为获得解决方案而生成的查询是：

SELECT ProductId
FROM   (SELECT DISTINCT ProductId,
                        DeliveryId
        FROM   ProductDeliveryModes) X
WHERE  X.DeliveryId IN ( 'D1', 'D2', 'D3' )
GROUP  BY ProductId
HAVING COUNT(*) = 3 

我在我的解决方案中看到的问题是，人们应该知道交付方式的总数。我们可以通过从子查询中获取计数来使计数动态。

有没有更好的解决办法？

Answer 1

我相信您可以使用DISTINCT和COUNT函数来获得相同的结果：

SELECT [ProductID]
FROM ProductDeliveryModes
GROUP BY  [ProductID]
HAVING COUNT(DISTINCT [DeliveryId]) = 3

检查一下示例。

您可以简单地将不同的传递计数存储在变量中并使用它。如果您需要在一个查询中这样做，这是可能的方法之一：

WITH CTE (DeliveryCount) AS 
(
   SELECT COUNT(DISTINCT [DeliveryID])
   FROM DataSource
)  
SELECT [ProductID]
FROM DataSource
CROSS APPLY CTE
GROUP BY [ProductID]
        ,CTE.DeliveryCount
HAVING COUNT(DISTINCT [DeliveryID]) = DeliveryCount

见示例。

Answer 2

为了获得更好的性能，您可以使用下面的查询。

 ;WITH CTE_Product
 AS
 (
        SELECT DISTINCT ProductID 
        FROM ProductDeliveryModes
 ),CTE_Delivery
 AS
 (
        SELECT DISTINCT DeliveryId 
        FROM ProductDeliveryModes
 )

 SELECT * 
 FROM CTE_Product C
 WHERE NOT EXISTS
 (
     SELECT 1 
     FROM CTE_Delivery D
     LEFT JOIN ProductDeliveryModes T ON T.DeliveryId = D.DeliveryId AND T.ProductId=C.ProductId 
     WHERE T.ProductID IS NULL
 ) 

Answer 3

您可以稍微修改查询，以获得不同传递方法的实际计数：

SELECT ProductID
FROM ProductDeliveryModes
GROUP BY ProductID
HAVING COUNT(*) =
   (SELECT COUNT (DISTINCT DeliveryId) FROM ProductDeliveryModes)