关闭

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

934人阅读 评论(0) 收藏 举报
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,透视表展示

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:182322次
    • 积分:3381
    • 等级:
    • 排名:第9904名
    • 原创:154篇
    • 转载:0篇
    • 译文:3篇
    • 评论:30条
    文章分类
    最新评论