BISM TABULAR MODEL 分集(EXCEPT)一个实例

use ExcelExercise
go
--===== Create the test table
 CREATE TABLE Purchase
        (
         PurchaseID     INT IDENTITY(1,1),
         CustomerID     INT,
         ProductCode    CHAR(1)
         PRIMARY KEY CLUSTERED (PurchaseID)
        )
;
--===== Populate the test table with known data.
 INSERT INTO Purchase
        (CustomerID, ProductCode)
------- Customer #1 precisely meets the criteria.
     -- Bought 'A' and 'B' but not 'C'.
 SELECT 1, 'A' UNION ALL
 SELECT 1, 'B' UNION ALL
------- Customer #2 also meets the criteria.
     -- Bought 'A' and 'B' and somthing else,
     -- but not 'C'.
 SELECT 2, 'A' UNION ALL
 SELECT 2, 'B' UNION ALL
 SELECT 2, 'D' UNION ALL
------- Customer #3 also meets the criteria.
     -- Bought 'A' and 'B' and something else,
     -- but not 'C'.
 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
------- Customer #4 doesn't meet the criteria.
     -- Bought 'A' and 'B' but also bought 'C'.
 SELECT 4, 'A' UNION ALL
 SELECT 4, 'B' UNION ALL
 SELECT 4, 'C' UNION ALL
------- Customer #5 doesn't meet the criteria.
     -- Bought 'A' and 'B' and something else,
     -- but also bought 'C'.
 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
------- Customer #6 doesn't meet the criteria.
     -- Bought more than 1 of 'A' and something else
     -- but not 'B'.
 SELECT 6, 'A' UNION ALL
 SELECT 6, 'A' UNION ALL
 SELECT 6, 'D' UNION ALL
 SELECT 6, 'E' UNION ALL
------- Customer #7 doesn't meet the criteria.
     -- Bought more than 1 of 'B' and something else
     -- but not 'A'.
 SELECT 7, 'B' UNION ALL
 SELECT 7, 'B' UNION ALL
 SELECT 7, 'D' UNION ALL
 SELECT 7, 'E'
;

对于这样的表,我们想要的顾客选择:

产品A和B,但不包括C.

在T-SQL中，可以用EXCEPT

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')

那么，在BISM TABULAR MODEL 如何实现呢？

1，导入数据

2，分别计算包含产品A,B,C

3，IF逻辑判断

4，透视表展示