求解一复杂问题
已经有数据库表，存放内容示例
id（编号） compName addDate addValue
1 cmp1 1 100
2 cmp1 2 332
3 cmp2 1 546
4 cmp4 3 450
5 cmp3 3 1000
....
现求一视图，结果为
编号 公司名称 一月份 二月份 三月份 ... 十二月 总计
1 cmp1 100 332
2 cmp2 无记录 2
.......
[解决办法]
select 公司名称,
sum(case when adddate = 1 then addvalue else 0 end) as '一月份 ',
sum(case when adddate = 2 then addvalue else 0 end) as '二月份 ',
sum(case when adddate = 3 then addvalue else 0 end) as '三月份 ',
sum(case when adddate = 4 then addvalue else 0 end) as '四月份 ',
sum(case when adddate = 5 then addvalue else 0 end) as '五月份 ',
sum(case when adddate = 6 then addvalue else 0 end) as '六月份 ',
sum(case when adddate = 7 then addvalue else 0 end) as '七月份 ',
sum(case when adddate = 8 then addvalue else 0 end) as '八月份 ',
sum(case when adddate = 9 then addvalue else 0 end) as '九月份 ',
sum(case when adddate = 10 then addvalue else 0 end) as '十月份 ',
sum(case when adddate = 11 then addvalue else 0 end) as '十一月份 ',
sum(case when adddate = 12 then addvalue else 0 end) as '十二月份 '
from tb
group by 公司名称
[解决办法]
Create View V_TEST
As
Select
compName As 公司名称,
SUM(Case addDate When 1 Then addValue Else 0 End) As 一月份,
SUM(Case addDate When 2 Then addValue Else 0 End) As 二月份,
SUM(Case addDate When 3 Then addValue Else 0 End) As 三月份,
SUM(Case addDate When 4 Then addValue Else 0 End) As 四月份,
SUM(Case addDate When 5 Then addValue Else 0 End) As 五月份,
SUM(Case addDate When 6 Then addValue Else 0 End) As 六月份,
SUM(Case addDate When 7 Then addValue Else 0 End) As 七月份,
SUM(Case addDate When 8 Then addValue Else 0 End) As 八月份,
SUM(Case addDate When 9 Then addValue Else 0 End) As 九月份,
SUM(Case addDate When 10 Then addValue Else 0 End) As 十月份,
SUM(Case addDate When 11 Then addValue Else 0 End) As 十一月份,
SUM(Case addDate When 12 Then addValue Else 0 End) As 十二月份
From
表
Group By
compName
GO
[解决办法]
行列转换加合计
例如有表A某些人每月消费数据如下：
id data month
001 11 1
001 12 2
001 13 3
001 14 4
001 15 5
001 16 6
001 17 7
001 18 8
001 19 9
001 110 10
001 111 11
001 112 12
002 21 1
002 22 2
002 23 3
002 24 4
002 25 5
002 26 6
002 27 7
002 28 8
002 29 9
002 210 10
002 211 11
002 212 12
要实现如下结果：
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212
create table 表A
(
id char(3),
data int,
month int
)
insert into 表A(id,data,month) values( '001 ',11,1)
insert into 表A(id,data,month) values( '001 ',12,2)
insert into 表A(id,data,month) values( '001 ',13,3)
insert into 表A(id,data,month) values( '001 ',14,4)
insert into 表A(id,data,month) values( '001 ',15,5)
insert into 表A(id,data,month) values( '001 ',16,6)
insert into 表A(id,data,month) values( '001 ',17,7)
insert into 表A(id,data,month) values( '001 ',18,8)
insert into 表A(id,data,month) values( '001 ',19,9)
insert into 表A(id,data,month) values( '001 ',110,10)
insert into 表A(id,data,month) values( '001 ',111,11)
insert into 表A(id,data,month) values( '001 ',112,12)
insert into 表A(id,data,month) values( '002 ',21,1)
insert into 表A(id,data,month) values( '002 ',22,2)
insert into 表A(id,data,month) values( '002 ',23,3)
insert into 表A(id,data,month) values( '002 ',24,4)
insert into 表A(id,data,month) values( '002 ',25,5)
insert into 表A(id,data,month) values( '002 ',26,6)
insert into 表A(id,data,month) values( '002 ',27,7)
insert into 表A(id,data,month) values( '002 ',28,8)
insert into 表A(id,data,month) values( '002 ',29,9)
insert into 表A(id,data,month) values( '002 ',210,10)
insert into 表A(id,data,month) values( '002 ',211,11)
insert into 表A(id,data,month) values( '002 ',212,12)
go
SELECT id as '人员 ' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月 ' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月 ' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月 ' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月 ' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月 ' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月 ' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月 ' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月 ' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月 ' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月 ' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月 ' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月 '
FROM 表A
GROUP BY ID
drop table 表A
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
---- --- --- --- --- --- --- --- --- --- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212
（所影响的行数为 2 行）
合计每个人每年的数据
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
SELECT id as '人员 ' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月 ' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月 ' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月 ' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月 ' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月 ' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月 ' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月 ' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月 ' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月 ' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月 ' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月 ' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月 ' ,
SUM(data) as '合计 '
FROM 表A
GROUP BY ID
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
---- --- --- --- --- --- --- --- --- --- ---- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
[解决办法]
如果不需要第一列，就可以像上面那。
如果需要第一列就麻些了。
[解决办法]
--也不麻烦,插入临时表
select id = identity(int,1,1),* into test from
(
select 公司名称,
sum(case when adddate = 1 then addvalue else 0 end) as '一月份 ',
sum(case when adddate = 2 then addvalue else 0 end) as '二月份 ',
sum(case when adddate = 3 then addvalue else 0 end) as '三月份 ',
sum(case when adddate = 4 then addvalue else 0 end) as '四月份 ',
sum(case when adddate = 5 then addvalue else 0 end) as '五月份 ',
sum(case when adddate = 6 then addvalue else 0 end) as '六月份 ',
sum(case when adddate = 7 then addvalue else 0 end) as '七月份 ',
sum(case when adddate = 8 then addvalue else 0 end) as '八月份 ',
sum(case when adddate = 9 then addvalue else 0 end) as '九月份 ',
sum(case when adddate = 10 then addvalue else 0 end) as '十月份 ',
sum(case when adddate = 11 then addvalue else 0 end) as '十一月份 ',
sum(case when adddate = 12 then addvalue else 0 end) as '十二月份 '
from tb
group by 公司名称
) t
select * from test
[解决办法]
--也不麻烦,插入临时表
select 编号 = identity(int,1,1),* into test from
(
select 公司名称,
sum(case when adddate = 1 then addvalue else 0 end) as '一月份 ',
sum(case when adddate = 2 then addvalue else 0 end) as '二月份 ',
sum(case when adddate = 3 then addvalue else 0 end) as '三月份 ',
sum(case when adddate = 4 then addvalue else 0 end) as '四月份 ',
sum(case when adddate = 5 then addvalue else 0 end) as '五月份 ',
sum(case when adddate = 6 then addvalue else 0 end) as '六月份 ',
sum(case when adddate = 7 then addvalue else 0 end) as '七月份 ',
sum(case when adddate = 8 then addvalue else 0 end) as '八月份 ',
sum(case when adddate = 9 then addvalue else 0 end) as '九月份 ',
sum(case when adddate = 10 then addvalue else 0 end) as '十月份 ',
sum(case when adddate = 11 then addvalue else 0 end) as '十一月份 ',
sum(case when adddate = 12 then addvalue else 0 end) as '十二月份 ',
sum(addvalue) as 总计
from tb
group by 公司名称
) t
select * from test