在SQL Server中生成交叉报表

最新推荐文章于 2019-08-18 08:45:24 发布

weixin_30553777

最新推荐文章于 2019-08-18 08:45:24 发布

阅读量143

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/stu-acer/archive/2006/07/21/456347.html

本文介绍如何使用SQL Server 2000及2005创建交叉报表，具体包括按季度汇总各商店销售数量，并计算全年总计。示例中详细展示了SQL语句的编写过程，适用于需要进行复杂数据汇总分析的场景。

/* Question 4
Can you create a cross-tab report in my SQL Server?
How can I get the report about sale quantity for each store and each quarter
and the total sale quantity for each quarter at year 1993?

You can use the table sales and stores in database pubs.
Table Sales record all sale detail item for each store, column store_id is the id of each store,
ord_date is the order date of each sale item, and column qty is the sale quantity.
Table stores record all store information.

I want to get the result look like as below:

Output:

stor_name                                Total       Qtr1        Qtr2        Qtr3        Qtr4
---------------------------------------- ----------- ----------- -------------------------------
Barnum's                                 50          0           50          0           0
Bookbeat                                 55          25          30          0           0
Doc-U-Mat: Quality Laundry and Books     85          0           85          0           0
Fricative Bookshop                       60          35          0           0           25
Total                                    250         60          165         0           25

*/
参考答案：
--SQL Server 2000生成交叉表
SELECT stor_name,
SUM(qty) AS 'Total',
SUM(CASE datepart(qq, ord_date) WHEN 1 THEN qty ELSE 0 END) AS Qtr1,
SUM(CASE datepart(qq, ord_date) WHEN 2 THEN qty ELSE 0 END) AS Qtr2,
SUM(CASE datepart(qq, ord_date) WHEN 3 THEN qty ELSE 0 END) AS Qtr3,
SUM(CASE datepart(qq, ord_date) WHEN 4 THEN qty ELSE 0 END) AS Qtr4
FROM sales s INNER JOIN stores st ON s.stor_id = st.stor_id
WHERE year(ord_date) = 1993
GROUP BY stor_name
Union
SELECT 'Total',
SUM(qty) AS 'Total',
SUM(CASE datepart(qq, ord_date) WHEN 1 THEN qty ELSE 0 END) AS Qtr1,
SUM(CASE datepart(qq, ord_date) WHEN 2 THEN qty ELSE 0 END) AS Qtr2,
SUM(CASE datepart(qq, ord_date) WHEN 3 THEN qty ELSE 0 END) AS Qtr3,
SUM(CASE datepart(qq, ord_date) WHEN 4 THEN qty ELSE 0 END) AS Qtr4
FROM sales s INNER JOIN stores st ON s.stor_id = st.stor_id
WHERE year(ord_date) = 1993

--SQL Server 2005生成交叉表
select stor_name, isnull([0],0) as 'Total',
    isnull([1],0) as 'Qtr1',isnull([2],0) as 'Qtr2',
    isnull([3],0) as 'Qtr3', isnull([4],0) as 'Qtr4'
from
(
select isnull(t.stor_name, 'Total') as 'stor_name',
     isnull(datepart(qq, ord_date),0) as 'Qtr', sum(qty) as 'qty'
from sales s
join stores t on s.stor_id = t.stor_id
where year(s.ord_date) = 1993
group by datepart(qq, ord_date), t.stor_name with cube
) as tmp
pivot
(
sum(qty) for Qtr in ([0], [1], [2], [3], [4])
) as pvt

这是我们公司培训的一次小考试中的题目，上面用的数据库是pubs。后面那种解法真牛。