主页
基础类
应用实例
新技术前沿

求sql语句??等

lilysl 2007-12-20 04:35:18

表为: table

id name val month
101 aaa 100.00 0
102 bbb 555.00 0
103 bbb 700.00 1
104 ddd 895.00 1
105 eee 700.00 0
106 aaa 800.00 1

想要的查询结果：

name val val_1
aaa 800.00 100.00
bbb 700.00 555.00
ddd 895.00 0
eee 0 700.00

...全文

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

dawugui 2007-12-21

/*
按月进行行列转换并加合计(2007-11-19于海南三亚)

例如有表tb某些人每月消费数据如下：
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 tb
(
id char(3),
data int,
month int
)
insert into tb(id,data,month) values('001',11,1)
insert into tb(id,data,month) values('001',12,2)
insert into tb(id,data,month) values('001',13,3)
insert into tb(id,data,month) values('001',14,4)
insert into tb(id,data,month) values('001',15,5)
insert into tb(id,data,month) values('001',16,6)
insert into tb(id,data,month) values('001',17,7)
insert into tb(id,data,month) values('001',18,8)
insert into tb(id,data,month) values('001',19,9)
insert into tb(id,data,month) values('001',110,10)
insert into tb(id,data,month) values('001',111,11)
insert into tb(id,data,month) values('001',112,12)
insert into tb(id,data,month) values('002',21,1)
insert into tb(id,data,month) values('002',22,2)
insert into tb(id,data,month) values('002',23,3)
insert into tb(id,data,month) values('002',24,4)
insert into tb(id,data,month) values('002',25,5)
insert into tb(id,data,month) values('002',26,6)
insert into tb(id,data,month) values('002',27,7)
insert into tb(id,data,month) values('002',28,8)
insert into tb(id,data,month) values('002',29,9)
insert into tb(id,data,month) values('002',210,10)
insert into tb(id,data,month) values('002',211,11)
insert into tb(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 tb
GROUP BY ID

drop table tb

/*
人员 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 tb
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
*/

回复赞

wangxuelid 2007-12-21

create table tb(id int, name varchar(10), val int, [month] int )
insert into tb values(101, 'aaa', 100.00 , 0 )
insert into tb values(102, 'bbb', 555.00 , 0 )
insert into tb values(103, 'bbb', 700.00 , 1 )
insert into tb values(104, 'ddd', 895.00 , 1 )
insert into tb values(105, 'eee', 700.00 , 0 )
insert into tb values(106, 'aaa', 800.00 , 1 )
go

select name, max(case month when 1 then val else 0 end)var_1 ,max(case month when 0 then val else 0 end )val
from tb group by name

回复赞

Haten 2007-12-20

this:

select name ,
sum(case [month] when 1 then val else 0 end) val,
sum(case [month] when 0 then val else 0 end) val_1
from tb
group by name

回复赞

Haten 2007-12-20

create table tb(id int, name varchar(10), val int, [month] int )
insert into tb values(101, 'aaa', 100.00 , 0 )
insert into tb values(102, 'bbb', 555.00 , 0 )
insert into tb values(103, 'bbb', 700.00 , 1 )
insert into tb values(104, 'ddd', 895.00 , 1 )
insert into tb values(105, 'eee', 700.00 , 0 )
insert into tb values(106, 'aaa', 800.00 , 1 )
go

select name ,
sum(case [month] when 1 then val else val end) val,
sum(case [month] when 0 then val else val end) val_1
from tb

回复赞

wzy_love_sly 2007-12-20

老龟少end了

回复赞

dawugui 2007-12-20

create table tb(id int, name varchar(10), val int, [month] int )

insert into tb values(101, 'aaa', 100.00 , 0 )

insert into tb values(102, 'bbb', 555.00 , 0 )

insert into tb values(103, 'bbb', 700.00 , 1 )

insert into tb values(104, 'ddd', 895.00 , 1 )

insert into tb values(105, 'eee', 700.00 , 0 )

insert into tb values(106, 'aaa', 800.00 , 1 )

go



select name , 

 sum(case [month] when 1 then val else 0 end) val, 

 sum(case [month] when 0 then val else 0 end) val_1 

from tb 

group by name 



drop table tb



/*

name       val         val_1       

---------- ----------- ----------- 

aaa        800         100

bbb        700         555

ddd        895         0

eee        0           700



（所影响的行数为 4 行）

*/

回复赞

dawugui 2007-12-20

select name ,
sum(case month when 1 then val else 0) val,
sum(case month when 0 then val else 0) val_1
from tb
group by name

回复赞

发动态

发帖子

MS-SQL Server

创建于2007-09-28

加入

3.2w+

社区成员

25.3w+

社区内容

MS-SQL Server相关内容讨论专区

申请成为版主

社区公告

暂无公告