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，透视表展示