在SQL Server中生成交叉报表

本文介绍如何使用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。后面那种解法真牛。

转载于:https://www.cnblogs.com/stu-acer/archive/2006/07/21/456347.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值