《sql报表查询技艺》由会员分享,可在线阅读,更多相关《sql报表查询技艺(12页珍藏版)》请在金锄头文库上搜索。
1、SQLSQL 报表查询技艺报表查询技艺SQL SERVER 中的 union,cube,rollup 和 cumpute 运算符UNION 运算符是将两个或更多查询的结果组合为单个结果集 该结果集包含联合查询中的所有查询的全部行。这与使用联接组合两个表中的列不同。 使用 UNION 组合查询的结果集有两个最基本的规则: 1。所有查询中的列数和列的顺序必须相同。 2。数据类型必须兼容 a.UNION 的结果集列名与第一个 select 语句中的结果集中的列名相同,其他 select 语句的结果集列名被忽略 b.默认情况下,UNION 运算符是从结果集中删除重复行。如果使用all 关键字,那么结果
2、集将包含所有行并且不删除重复行 c.sql 是从左到右对包含 UNION 运算符的语句进行取值,使用括号可以改变求值顺序 -例如: select * from tablea union all ( select * from tableb union all select * from tablec ) 这样就可以先对 tableb 和 tablec 合并,再合并 tablea d.如果要将合并后的结果集保存到一个新数据表中,那么 into 语句必须加入到第一条 select 中 e.只可以在最后一条 select 语句中使用 order by 和 compute 子句,这样影响到最终合并结果
3、的排序和计数汇总 f.group by 和 having 子句可以在单独一个 select 查询中使用,它们不影响最终结果 -例如: select name as 姓名,class as 班级,grade as 年级 into #students from stud87 union all select * from stud88 union all select * from stud89 order by 年级 将 3 个班级的合并结果(按 grade 排序)插入到临时表 #students里面 用 CUBE 汇总数据CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展,事
4、实数据即记录个别事件的数据。扩展建立在用户打算分析的列上。这些列被称为维。多维数据集是一个结果集,其中包含了各维度的所有可能组合的交叉表格。CUBE 运算符在 SELECT 语句的 GROUP BY 子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUP BY 应指定维度列和关键字 WITH CUBE。结果集将包含维度列中各值的所有可能组合,以及与这些维度值组合相匹配的基础行中的聚合值。例如,一个简单的表 Inventory 中包含:Item Color Quantity - - - Table Blue 124 Table Red 223 Chair Blue 101 Chai
5、r Red 210 下列查询返回的结果集中,将包含 Item 和 Color 的所有可能组合的 Quantity 小计:SELECT Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE下面是结果集:Item Color QtySum - - - Chair Blue 101.00 Chair Red 210.00 Chair (null) 311.00 Table Blue 124.00 Table Red 223.00 Table (null) 347.00 (null) (null
6、) 658.00 (null) Blue 225.00 (null) Red 433.00 我们着重考查下列各行:Chair (null) 311.00 这一行报告了 Item 维度中值为 Chair 的所有行的小计。对 Color 维度返回了 NULL 值,表示该行所报告的聚合包括 Color 维度为任意值的行。Table (null) 347.00 这一行类似,但报告的是 Item 维度中值为 Table 的所有行的小计。(null) (null) 658.00 这一行报告了多维数据集的总计。Item 和 Color 维度的值都是 NULL,表示两个维度中的所有值都汇总在该行中。(null
7、) Blue 225.00 (null) Red 433.00 这两行报告了 Color 维度的小计。两行中的 Item 维度值都是 NULL,表示聚合数据来自 Item 维度为任意值的行。使用 GROUPING 区分空值CUBE 操作所生成的空值带来一个问题:如何区分 CUBE 操作所生成的 NULL 值和从实际数据中返回的 NULL 值?这个问题可用 GROUPING 函数解决。如果列中的值来自事实数据,则 GROUPING 函数返回 0;如果列中的值是 CUBE 操作所生成的 NULL,则返回 1。在 CUBE 操作中,所生成的 NULL 代表全体值。可将 SELECT 语句写成使用 G
8、ROUPING 函数将所生成的 NULL 替换为字符串 ALL。因为事实数据中的 NULL 表明数据值未知,所以 SELECT 语句还可译码为返回字符串 UNKNOWN 替代来自事实数据的 NULL。例如:SELECT CASE WHEN (GROUPING(Item) = 1) THEN ALLELSE ISNULL(Item, UNKNOWN)END AS Item,CASE WHEN (GROUPING(Color) = 1) THEN ALLELSE ISNULL(Color, UNKNOWN)END AS Color,SUM(Quantity) AS QtySumFROM Inven
9、toryGROUP BY Item, Color WITH CUBE多维数据集CUBE 运算符可用于生成 n 维的多维数据集,即具有任意数目维度的多维数据集。只有一个维度的多维数据集可用于生成合计,例如:SELECT CASE WHEN (GROUPING(Item) = 1) THEN ALLELSE ISNULL(Item, UNKNOWN)END AS Item,SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item WITH CUBEGO此 SELECT 语句返回的结果集既显示了 Item 中每个值的小计,也显示了 Item 中所有值的总计
10、:Item QtySum - - Chair 311.00 Table 347.00 ALL 658.00 包含带有许多维度的 CUBE 的 SELECT 语句可能生成很大的结果集,因为这些语句会为所有维度中值的所有组合生成行。这些大结果集包含的数据可能过多而不易于阅读和理解。这个问题有一种解决办法是将 SELECT 语句放在视图中:CREATE VIEW InvCube ASSELECT CASE WHEN (GROUPING(Item) = 1) THEN ALLELSE ISNULL(Item, UNKNOWN)END AS Item,CASE WHEN (GROUPING(Color)
11、 = 1) THEN ALLELSE ISNULL(Color, UNKNOWN)END AS Color,SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE然后即可用该视图来只查询您感兴趣的维度值:SELECT *FROM InvCubeWHERE Item = ChairAND Color = ALLItem Color QtySum - - - Chair ALL 311.00 (1 row(s) affected)用 ROLLUP 汇总数据在生成包含小计和合计的报表时,ROLLUP 运算符很有用。ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。有关更多信息,请参见用 CUBE 汇总数据。 CUBE 和 ROLLUP 之间的区别在于: CUBE 生成的结果集显示了所选列中值的所有组合的聚合。ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。 例如,简单表 Inventory 中包含:Item Color Quantity -