SQL完全教程第5章高级查询3-交叉表查询.ppt

知识回顾 第5章 高级查询 5.4 交叉表查询 功能:使用交叉表查询可以直观而清晰的反映数据之间的关系,它可以重新生成一种方便阅读的数据表格 5.4 交叉表查询 5.4.1 CASE运算符实现交叉表查询 5.4.2 PIVOT运算符实现交叉表查询 5.4.3 UNPIVOT运算符实现交叉表查询 5.4.1 CASE运算符实现交叉表查询 例1：查询 porders表中每个月每个产品的总销量 实现语句： SELECT productid,sum(CASE shipmonth WHEN 1 then quantity ELSE NULL END)一月销量 , sum(case shipmonth WHEN 2 then quantity else NULL END) 二月销量, sum(case shipmonth WHEN 3 then quantity else NULL END) 三月销量, sum(case shipmonth WHEN 4 then quantity else NULL END) 四月销量, sum(case shipmonth WHEN 5 then quantity else NULL END) 五月销量, sum(case shipmonth WHEN 6 then quantity else NULL END) 六月销量, sum(case shipmonth WHEN 7 then quantity else NULL END) 七月销量, sum(case shipmonth WHEN 8 then quantity else NULL END) 八月销量, sum(case shipmonth WHEN 9 then quantity else NULL END) 九月销量, sum(case shipmonth WHEN 10 then quantity else NULL END) 十月销量, sum(case shipmonth WHEN 11 then quantity else NULL END) 十一月销量, sum(case shipmonth WHEN 12 then quantity else NULL END) 十二月销量 FROM porders GROUP BY productid GO 5.4.1 CASE运算符实现交叉表查询 练习：查询 PUBS数据库中sales表各仓库每年的销售总量 实现语句： USE pubs GO SELECT stor_id,sum(CASE year(ord_date) WHEN 1993 then qty ELSE NULL END) 九三年销量 ,sum(CASE year(ord_date) WHEN 1994 then qty ELSE NULL END) 九四年销量 FROM sales GROUP BY stor_id GO 5.4 交叉表查询 5.4.1 CASE运算符实现交叉表查询 5.4.2 PIVOT运算符实现交叉表查询 5.4.3 UNPIVOT运算符实现交叉表查询 5.4.2 PIVOT运算符实现交叉表查询 例2：查询 porders表中每个月每个产品的总销量 实现语句： USE northwind GO SELECT * FROM porders PIVOT(SUM(quantity) FOR shipmonth IN([1],[2],[3],[4],[5],[6], [7],[8],[9],[10],[11],[12])) AS p ORDER BY productid GO 5.4.2 PIVOT运算符实现交叉表查询 结果分析： 1）PIVOT首先按列quantity之外的列（productid,shipmonth）对表进行分组汇总 2) PIVOT根据 FOR shipmonth IN 指定的值从中间结果中取出与shipmonth 相符合的值，分别放到各列中，并得到结果集P 3）最后根据SELECT语句后的内容显示相应的列，得到最后结果集 5.4.2 PIVOT运算符实现交叉表查询 练习：查询 PUBS数据库中sales表各仓库每年的销售总量 实现语句： SELECT stor_id,year(ord_date) saleyear,qty INTO newSales FROM sales GO SELECT * FROM newsales PIVOT(sum(qty) FOR saleyear IN([1993],[1994]))AS p ORDER BY stor_id GO 5.4 交叉表查询 5.4.1 CASE运算符实现交叉表查询 5