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,透视表展示
分享到:
相关推荐
内网的百度,功能强大的FTP搜索引擎. 针对大型局域网和城域网设计。 这款引擎为局域网用户提供了很大的方便,用户可以精确,快速地通过引擎查找到自己想要的资源,如课件,软件,电影等等。...开发者:BISM软件工作室
BISM菁搜ftp搜索引擎开发文档 这是BISM菁搜ftp搜索引擎的主要开发文档 开源BLOG: http://blog.csdn.net/bismsoftware
专门针对大中型的局域网和城域网的FTP搜索引擎。 开发者:BISM软件开发小组 相当于内网百度与GOOGLE
开源项目:BISM“菁搜”FTP搜索引擎 borland c++ 2007开发平台 这里使用了boost库 联系EMAIL:BISM软件工作组 E-MAIL:Bismsotware@163.com 余文锋 E-MAIL:wenfeng610@yahoo.com.cn 陈鸿雏 E-MAIL:canzer@126....
开源项目:BISM“菁搜”FTP搜索引擎 borland c++ 2007开发平台 这里使用了boost库 联系EMAIL:BISM软件工作组 E-MAIL:Bismsotware@163.com 余文锋 E-MAIL:wenfeng610@yahoo.com.cn 陈鸿雏 E-MAIL:canzer@126....
Visual C# 2005 asp.net 2.0 ... 有相关问题可以联系我们...BISM软件工作组 E-MAIL:Bismsotware@163.com 余文锋 E-MAIL:wenfeng610@yahoo.com.cn 陈鸿雏 E-MAIL:canzer@126.com.cn 吴侃侃 E-MAIL:suzerain2005@163.com
这是我为商务签证管理准备的第一个开放源代码项目,准备为我的签证面试做准备,这可以使我Swift理解计算机不敏感的信息。
比较全的BSIM4模型资料,包含修订及各年代修订时的C语言代码,部分manual手册 BSIM模型及SPICE语言等原版资料 A. BSIM4.0.0 released on 03/24/2000 ...总共包含四个压缩包。 C语言 , Manual , 模型 , 手册 , 资料
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
BISM, Microsofts new Business Intelligence Semantic Model; and much more. This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written ...
巴鲁夫 识别系统BIS Mpdf,该资料内容包括识别系统BIS M产品的编码块、读/ 写头、插头等介绍
而无需GUI AsXEventSample示例展示了如何收集流式xEvent BISM Normalizer是用于表格模型的架构差异工具示例U-SQL脚本,它们演示了如何在Azure Data Lake中处理TPC-DS数据集。ASJobGraph Python脚本,用于重组来自...
为了实现这一目的,你将在本书中了解到如何使用SQL Server Analysis Services设计、构建和处理多维和表格商业智能语义模型(BISM)。随后,你将使用MDX和DAX来查询这些数据库,以便可以针对业务问题提供高级分析,并...