SQL 集合例子

IF OBJECT_ID('tempdb..#Purchase', 'U') IS NOT NULL
DROP TABLE #Purchase;

CREATE TABLE #Purchase
(
PurchaseID INT IDENTITY(1, 1) ,
CustomerID INT ,
ProductCode CHAR(1) PRIMARY KEY CLUSTERED ( PurchaseID )
);
INSERT INTO #Purchase
( CustomerID ,
ProductCode
)
SELECT 1 ,
'A'
UNION ALL
SELECT 1 ,
'B'
UNION ALL
SELECT 2 ,
'A'
UNION ALL
SELECT 2 ,
'B'
UNION ALL
SELECT 2 ,
'D'
UNION ALL
SELECT 3 ,
'A'
UNION ALL
SELECT 3 ,
'B'
UNION ALL
SELECT 3 ,
'D'
UNION ALL
SELECT 3 ,
'A'
UNION ALL
SELECT 3 ,
'D'
UNION ALL
SELECT 4 ,
'A'
UNION ALL
SELECT 4 ,
'B'
UNION ALL
SELECT 4 ,
'C'
UNION ALL
SELECT 5 ,
'A'
UNION ALL
SELECT 5 ,
'B'
UNION ALL
SELECT 5 ,
'A'
UNION ALL
SELECT 5 ,
'B'
UNION ALL
SELECT 5 ,
'C'
UNION ALL
SELECT 5 ,
'D'
UNION ALL
SELECT 6 ,
'A'
UNION ALL
SELECT 6 ,
'A'
UNION ALL
SELECT 6 ,
'D'
UNION ALL
SELECT 6 ,
'E'
UNION ALL
SELECT 7 ,
'B'
UNION ALL
SELECT 7 ,
'B'
UNION ALL
SELECT 7 ,
'D'
UNION ALL
SELECT 7 ,
'E'
UNION ALL
SELECT 8 ,
'A'
UNION ALL
SELECT 9 ,
'B';

SELECT a.CustomerID ,
a.PurchaseID ,
a.ProductCode
FROM #Purchase a
WHERE ProductCode IN ( 'a', 'b' )
AND NOT EXISTS ( SELECT *
FROM #Purchase b
WHERE a.CustomerID = b.CustomerID
AND ProductCode NOT IN ( 'a', 'b' ) );

 

SELECT a.CustomerID ,
a.PurchaseID ,
a.ProductCode
FROM #Purchase a
WHERE CustomerID IN (
SELECT CustomerID
FROM #Purchase b
WHERE ProductCode IN ( 'a', 'b' )
GROUP BY CustomerID
HAVING COUNT(DISTINCT ProductCode) = ( SELECT COUNT(DISTINCT ProductCode)
FROM #Purchase c
WHERE c.CustomerID = b.CustomerID
) );

 

SELECT CustomerID
FROM #Purchase
WHERE ProductCode IN ( 'A', 'B' )
GROUP BY CustomerID
HAVING COUNT(DISTINCT ProductCode) = 2
EXCEPT
--===== Find Customers that bought "C".
SELECT CustomerID
FROM #Purchase
WHERE ProductCode IN ( 'C' );

 

SELECT *
FROM #Purchase;