SQL Server：多对多

Question

我有一个多对多关系：Orders、OrderProducts 和 Products。我需要一个查询，为我提供并非所有订单中但已订购的产品列表。

______________________
|      ORDERS         |
_______________________
| OrderID | OrderDate |
| 1       | 1/2/2012  |
| 2       | 1/3/2012  |
| 3       | 1/4/2012  |
| 4       | 1/5/2012  |
| 5       | 1/6/2012  |

______________________
|      ORDERPRODUCTS  |
_______________________
| OrderID | PRODUCTID |
| 1       | 1         |
| 1       | 2         |
| 2       | 1         |
| 2       | 2         |
| 2       | 3         |
| 2       | 4         |
| 3       | 1         |
| 3       | 5         |
| 4       | 1         |
| 5       | 1         |

__________________________
|      PRODUCTS           |
__________________________
| PRODUCTID | PRODUCTNAME |
| 1         | Widget 1    |
| 2         | Widget 2    |
| 3         | Widget 3    |
| 4         | Widget 4    |
| 5         | Widget 5    |
| 6         | Widget 6    |

在提供的示例中，请注意，所有订单中都包含产品 1，而根本没有订购产品 6。

我需要一个返回产品 2、3、4 和 5 的查询。

另请记住，虽然产品不多，但实时数据库中有数十万个订单。

Answer 1

SELECT PRODUCTID 
FROM ORDERPRODUCTS  
GROUP BY PRODUCTID 
HAVING COUNT(DISTINCT OrderID) < (SELECT COUNT(*) FROM ORDERS )

Answer 2

现在是星期六晚上，所以这可能不是最优雅的，但这里有一个尝试：

DECLARE @OrderProducts TABLE(OrderID INT, ProductID INT);

DECLARE @Products TABLE(ProductID INT, ProductName VARCHAR(32));

INSERT @Products VALUES
(1,'Widget 1'),(2,'Widget 2'),
(3,'Widget 3'),(4,'Widget 4'),
(5,'Widget 5'),(6,'Widget 6');

INSERT @OrderProducts VALUES
(1,1),(1,2),(2,1),(2,2),(2,3),
(2,4),(3,1),(3,5),(4,1),(5,1);

SELECT p.ProductID, p.ProductName 
FROM @Products AS p
WHERE EXISTS -- had been ordered at least once
(
  SELECT 1 FROM @OrderProducts 
  WHERE ProductID = p.ProductID
)
AND EXISTS -- at least one order does NOT include it
(
  SELECT 1 FROM @OrderProducts AS o 
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM @OrderProducts AS o2 
    WHERE o2.OrderID = o.OrderID 
    AND o2.ProductID = p.ProductID
  )
);

Answer 3

SELECT
  DISTINCT
  PossibleOrderProducts.PRODUCTID
FROM
  (
  SELECT
    Orders.ORDERID,
    Products.PRODUCTID
  FROM
    ORDERS Orders
    CROSS JOIN
      (
      SELECT DISTINCT PRODUCTID FROM ORDERPRODUCTS
      ) Products
  ) PossibleOrderProducts
  LEFT JOIN ORDERPRODUCTS ActualOrderProducts ON
    ActualOrderProducts.ORDERID = PossibleOrderProducts.ORDERID
    AND ActualOrderProducts.PRODUCTID = PossibleOrderProducts.PRODUCTID
WHERE
  ActualOrderProducts.ORDERID IS NULL