create table t
(pay_month varchar(10),emp_no varchar(10),name varchar(10),rmb int)
insert t
select '200510','00001','aaa',10000 union all
select '200510','00002','bbb',7800 union all
select '200510','00006','ccc',6000 union all
select '200510','00008','ddd',5300 union all
select '200510','00011','eee',6300 union all
select '200510','00012','fff',3200 union all
select '200510','00013','ggg',4500 union all
select '200511','00001','aaa',12000 union all
select '200511','00002','bbb',8500 union all
select '200511','00006','ccc',6000 union all
select '200511','00008','ddd',5600 union all
select '200511','00011','eee',6500 union all
select '200511','00012','fff',3200 union all
select '200511','00015','hhh',5500 union all
select '200512','00001','aaa',9500 union all
select '200512','00002','bbb',8000 union all
select '200512','00008','ddd',5000 union all
select '200512','00011','eee',6500 union all
select '200512','00015','hhh',5600 union all
select '200512','00016','jjj',6900 declare @sql varchar(8000)
select @sql='select emp_no,name'
select @sql=@sql+',['+pay_month+']=sum(case when pay_month='''+pay_month+''' then rmb end)'
from t group by pay_month order by pay_month desc
select @sql=@sql+'from
(select * from t a where exists (select * from t b where a.name=b.name and b.pay_month=''200512'')) a
group by emp_no,name'
exec (@sql)
drop table temp_no name 200512 200511 200510
---------- ---------- ----------- ----------- -----------
00001 aaa 9500 12000 10000
00002 bbb 8000 8500 7800
00008 ddd 5000 5600 5300
00011 eee 6500 6500 6300
00015 hhh 5600 5500 NULL
00016 jjj 6900 NULL NULL警告: 聚合或其它 SET 操作消除了空值。
(pay_month varchar(10),emp_no varchar(10),name varchar(10),rmb int)
insert t
select '200510','00001','aaa',10000 union all
select '200510','00002','bbb',7800 union all
select '200510','00006','ccc',6000 union all
select '200510','00008','ddd',5300 union all
select '200510','00011','eee',6300 union all
select '200510','00012','fff',3200 union all
select '200510','00013','ggg',4500 union all
select '200511','00001','aaa',12000 union all
select '200511','00002','bbb',8500 union all
select '200511','00006','ccc',6000 union all
select '200511','00008','ddd',5600 union all
select '200511','00011','eee',6500 union all
select '200511','00012','fff',3200 union all
select '200511','00015','hhh',5500 union all
select '200512','00001','aaa',9500 union all
select '200512','00002','bbb',8000 union all
select '200512','00008','ddd',5000 union all
select '200512','00011','eee',6500 union all
select '200512','00015','hhh',5600 union all
select '200512','00016','jjj',6900 declare @sql varchar(8000)
select @sql='select emp_no,name'
select @sql=@sql+',['+pay_month+']=sum(case when pay_month='''+pay_month+''' then rmb end)'
from t group by pay_month order by pay_month desc
select @sql=@sql+'from
(select * from t a where exists (select * from t b where a.name=b.name and b.pay_month=''200512'')) a
group by emp_no,name'
exec (@sql)
drop table temp_no name 200512 200511 200510
---------- ---------- ----------- ----------- -----------
00001 aaa 9500 12000 10000
00002 bbb 8000 8500 7800
00008 ddd 5000 5600 5300
00011 eee 6500 6500 6300
00015 hhh 5600 5500 NULL
00016 jjj 6900 NULL NULL警告: 聚合或其它 SET 操作消除了空值。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货