SQL 查询每个员工不同年份的工资比较情况(极具挑战性）
0
悬赏园豆：20
[待解决问题]
ID SALARAY TIME1
1 100 2008-01-01 00:00:00
1 100 2008-02-01 00:00:00
1 100 2008-03-01 00:00:00
..
1 200 2009-01-01 00:00:00
1 200 2009-02-01 00:00:00
....
2 300 2008-01-01 00:00:00
......
我想得到的是：
ID 2008 2009
1 1200 2400
2 3600 .....
希望用join之类的解决
所有回答(5)
0
我想到的办法比较笨,还是等高手来解决吧.
0
-- 如果只用 join ，那么代码很长而且扩展性不佳
with data(id,sal,time1) as (
select 1,100,'2008-01-01 00:00:00' union all
select 1,100,'2008-02-01 00:00:00' union all
select 1,200,'2009-01-01 00:00:00' union all
select 1,200,'2009-02-01 00:00:00' union all
select 2,300,'2008-01-01 00:00:00' union all
select 2,300,'2008-01-01 00:00:00' union all
select 2,300,'2009-01-01 00:00:00' union all
select 2,300,'2009-01-01 00:00:00' union all
select 3,300,'2009-01-01 00:00:00' union all
select 4,300,'2008-01-01 00:00:00'
),
tmp (id,sal,years) as (
select id,sal,DATEPART(yyyy,time1) years from data
)
select
isnull(d2008.id,d2009.id) id,
d2008.sum_sal [2008] ,d2009.sum_sal [2009]
from
(
select id,SUM(sal) sum_sal from tmp
where years=2008
group by id
) d2008
full outer join
(
select id,SUM(sal) sum_sal from tmp
where years=2009
group by id
) d2009
on d2008.id=d2009.id
with data(id,sal,time1) as (
select 1,100,'2008-01-01 00:00:00' union all
select 1,100,'2008-02-01 00:00:00' union all
select 1,200,'2009-01-01 00:00:00' union all
select 1,200,'2009-02-01 00:00:00' union all
select 2,300,'2008-01-01 00:00:00' union all
select 2,300,'2008-01-01 00:00:00' union all
select 2,300,'2009-01-01 00:00:00' union all
select 2,300,'2009-01-01 00:00:00' union all
select 3,300,'2009-01-01 00:00:00' union all
select 4,300,'2008-01-01 00:00:00'
)
-->> start here
select id,[2008],[2009],[2010] from (
select id,sal,datepart(yyyy,time1) years from data
) a
PIVOT (
sum(sal)
FOR years IN ([2008], [2009],[2010]) --<< years
) AS pvt
id 2008 2009 2010
----------- ----------- ----------- -----------
1 200 400 NULL
2 600 600 NULL
3 NULL 300 NULL
4 300 NULL NULL
0
关注
0
明天试试killkill的方法.
0
提供一种解决方案,利用行列转换
declare @EMP Table
(
ID int,
Salary float,
TIME1 datetime
)
insert into @EMP values
(1 ,100 ,'2008-01-01 00:00:00'),
(1 ,100 ,'2008-02-01 00:00:00'),
(1 ,100 ,'2008-03-01 00:00:00'),
(1 ,200 ,'2009-01-01 00:00:00'),
(1 ,200 ,'2009-02-01 00:00:00'),
(2 ,300 ,'2008-01-01 00:00:00')
;with Cte_year
as(
select ID,Salary,Year(TIME1) as Year1 from @EMP
)
select ID,[2008],[2009] from Cte_year
pivot
(
sum(Salary) for Year1 in ([2008],[2009])
)as pvt
