首页 新闻 搜索 专区 学院

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之类的解决

长空无忌的主页 长空无忌 | 初学一级 | 园豆:120
提问于:2010-09-14 17:30
< >
分享
所有回答(5)
0

我想到的办法比较笨,还是等高手来解决吧.

I,Robot | 园豆:9563 (大侠五级) | 2010-09-14 19:08
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
可以考虑使用pivot 进行转置代码轻便很多:

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

 

killkill | 园豆:992 (小虾三级) | 2010-09-14 19:45
0

关注

paymob | 园豆:215 (菜鸟二级) | 2010-09-14 21:27
0

明天试试killkill的方法.

Jerry Young | 园豆:435 (菜鸟二级) | 2010-09-16 00:44
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

 

changbluesky | 园豆:854 (小虾三级) | 2010-09-16 10:37
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册