假设一个表,里面是一些人的月工资数据,如:
month name salary
1 甲 1500
1 乙 1300
.............
2 甲 1530
2 乙 1260
.............
3 甲 1580
3 乙 1360
.............
12 甲 1430
12 乙 1400
............. (“.....”表示省略)
我现在需要统计每个人每个季度的月平均工资,不知行不行,大家帮帮我呀!谢谢!
month name salary
1 甲 1500
1 乙 1300
.............
2 甲 1530
2 乙 1260
.............
3 甲 1580
3 乙 1360
.............
12 甲 1430
12 乙 1400
............. (“.....”表示省略)
我现在需要统计每个人每个季度的月平均工资,不知行不行,大家帮帮我呀!谢谢!
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',1300select
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
**/
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
day name day_total
2008-01-01 a 1000
2008-01-01 b 1100
2008-01-01 c 900
2008-01-01 d 1020
2008-01-01 e 810
......
2008-01-31 a 1310
2008-01-31 b 1000
2008-01-31 c 1600
2008-01-31 d 1010
2008-01-31 e 990我想统计每个人每4天的平均销售量,同时仍旧在同一列,例如:
name avg_4_total
a 1xxx //每4天的平均量
a 1xxx
a 9xx
...
b 1xxx
b 1xxx
b 1xxx
....
c 9xx
c 1xxx
c 1xxx
....
可以吗?谢谢啊~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~