sql小计汇总有关问题
日期：2014-05-17 浏览次数：20518 次
sql小计汇总问题
create table demo
(
Id int primary key identity(1,1),
Name nvarchar(20),
Month nvarchar(20),
Num int
)
insert demo values('A','2009-01',200)
insert demo values('A','2009-02',300)
insert demo values('A','2009-03',400)
insert demo values('B','2009-01',200)
insert demo values('B','2009-02',300)
insert demo values('B','2009-03',400)
insert demo values('A','2010-01',200)
Id Name Month Num
----------- -------------------- -------------------- -----------
8 A 2009-01 200
9 A 2009-02 300
10 A 2009-03 400
11 B 2009-01 200
12 B 2009-02 300
13 B 2009-03 400
14 A 2010-01 200
（所影响的行数为 7 行）
我想实现的结果是如下这样：
Name 2009-01 2009-02 2009-03 2009总计 2010-01 2010总计
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A 200 300 400 900 200 200
B 200 300 400 900 0 0
总计 400 600 800 1800 200 200
怎么实现呢，谢谢回答
------解决方案--------------------
create table demo
(
Id int primary key identity(1,1),
Name nvarchar(20),
Month nvarchar(20),
Num int
)
insert demo values('A','2009-01',200)
insert demo values('A','2009-02',300)
insert demo values('A','2009-03',400)
insert demo values('B','2009-01',200)
insert demo values('B','2009-02',300)
insert demo values('B','2009-03',400)
insert demo values('A','2010-01',200)
Id Name Month Num
----------- -------------------- -------------------- -----------
8 A 2009-01 200
9 A 2009-02 300
10 A 2009-03 400
11 B 2009-01 200
12 B 2009-02 300
13 B 2009-03 400
14 A 2010-01 200
（所影响的行数为 7 行）
我想实现的结果是如下这样：
Name 2009-01 2009-02 2009-03 2009总计 2010-01 2010总计
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A 200 300 400 900 200 200
B 200 300 400 900 0 0
总计 400 600 800 1800 200 200
怎么实现呢，谢谢回答
------解决方案--------------------
- SQL code
----------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2010-03-24 15:10:51
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[Name] varchar(1),[Month] varchar(7),[Num] int)
insert [tb]
select 8,'A','2009-01',200 union all
select 9,'A','2009-02',300 union all
select 10,'A','2009-03',400 union all
select 11,'B','2009-01',200 union all
select 12,'B','2009-02',300 union all
select 13,'B','2009-03',400 union all
select 14,'A','2010-01',200
--------------开始查询--------------------------
select
isnull([name],'总计') as name,
sum(case [Month] when '2009-01' then num else 0 end ) as '2009-01',
sum(case [Month] when '2009-02' then num else 0 end ) as '2009-02',
sum(case [Month] when '2009-03' then num else 0 end ) as '2009-03',
sum(case when left([month],4)='2009' then num else 0 end) as '2009总计',
sum(case [Month] when '2010-01' then num else 0 end ) as '2010-01',
sum(case when left([month],4)='2010' then num else 0 end) as '2010总计'
from
tb t
group by
[name]
with rollup
----------------结果----------------------------
/* name 2009-01 2009-02 2009-03 2009总计 2010-01 2010总计
---- ----------- ----------- ----------- ----------- ----------- -----------
A 200 300 400 900 200 200
B 200 300 400 900 0 0
400 &nb
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
