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