34,588
社区成员
发帖
与我相关
我的任务
分享
select name
,q1=(select avg(salary) from tb where name=a.name and month in (1,2,3))
,q2=(select avg(salary) from tb where name=a.name and month in (4,5,6))
,q3=(select avg(salary) from tb where name=a.name and month in (7,8,9))
,q4=(select avg(salary) from tb where name=a.name and month in (10,11,12))
from tb a
group by name
order by name
declare @t table
(
month_num int,
name char(1),
salary int
)
insert @t select 1,'a',1500
union all select 1,'b',1300
union all select 2,'a',1300
union all select 2,'b',1300
union all select 3,'a',1300
union all select 3,'b',1300
union all select 4,'a',1300
union all select 4,'b',1300
union all select 5,'a',1300
union all select 5,'b',1300
union all select 6,'a',1300
union all select 6,'b',1300
union all select 7,'a',1300
union all select 7,'b',1300
union all select 8,'a',1300
union all select 8,'b',1300
union all select 9,'a',1300
union all select 9,'b',1300
union all select 10,'a',1500
union all select 10,'b',1500
union all select 11,'a',1400
union all select 11,'a',1400
union all select 12,'b',1200
union all select 12,'b',1300
select
a.name,
q1 = (select sum(case when month_num in(1,2,3) then salary else 0 end) from @t where name = a.name) / 3.00,
q2 = (select sum(case when month_num in(4,5,6) then salary else 0 end) from @t where name = a.name) / 3.00,
q3 = (select sum(case when month_num in(7,8,9) then salary else 0 end) from @t where name = a.name) / 3.00 ,
q4 = (select sum(case when month_num in(10,11,12) then salary else 0 end) from @t where name = a.name) / 3.00
from @t a
group by a.name
/**
name q1 q2 q3 q4
------------------------------------------------------
a 1366.666666 1300.000000 1300.000000 1433.333333
b 1300.000000 1300.000000 1300.000000 1333.333333
**/