数据库table
年份 人数 工资
2008 10 20
2009 15 10
2008 5 6
2009 3 2
现在求出数据的总和
select sum(人数),sum(工资),年份 from table group by 年份想求出09年的人数和工资与08年的增减率多少?如何写语句或者代码?请高手帮忙!
年份 人数 工资
2008 10 20
2009 15 10
2008 5 6
2009 3 2
现在求出数据的总和
select sum(人数),sum(工资),年份 from table group by 年份想求出09年的人数和工资与08年的增减率多少?如何写语句或者代码?请高手帮忙!
case 人数08 when 0 then 0 else (人数09-人数08)/人数08 end as 人数增长率,
case 工资08 when 0 then 0 else (工资09-工资08)/工资08 end as 工资增长率,
from
(
select
sum(人数09) as 人数09,
sum(人数08) as 人数08,
sum(工资09) as 工资09,
sum(工资08) as 工资08
(
select
case 年份 when 2009 then 人数 else 0 end as 人数09,
case 年份 when 2008 then 人数 else 0 end as 人数08,
case 年份 when 2009 then 工资 else 0 end as 工资09,
case 年份 when 2008 then 工资 else 0 end as 工资08
from
(
select sum(人数) as 人数,sum(工资) as 工资,年份 from table group by 年份
) a
) b
) c
select
case 人数08 when 0 then 0 else (人数09-人数08)/人数08 end as 人数增长率,
case 工资08 when 0 then 0 else (工资09-工资08)/工资08 end as 工资增长率
from
(
select
sum(人数09) as 人数09,
sum(人数08) as 人数08,
sum(工资09) as 工资09,
sum(工资08) as 工资08
(
select
case 年份 when 2009 then 人数 else 0 end as 人数09,
case 年份 when 2008 then 人数 else 0 end as 人数08,
case 年份 when 2009 then 工资 else 0 end as 工资09,
case 年份 when 2008 then 工资 else 0 end as 工资08
from
(
select sum(人数) as 人数,sum(工资) as 工资,年份 from table group by 年份
) a
) b
) c
select
case 人数08 when 0 then 0 else (人数09-人数08)/人数08 end as 人数增长率,
case 工资08 when 0 then 0 else (工资09-工资08)/工资08 end as 工资增长率
from
(
select
sum(人数09) as 人数09,
sum(人数08) as 人数08,
sum(工资09) as 工资09,
sum(工资08) as 工资08
(
select
case 年份 when 2009 then 人数 else 0 end as 人数09,
case 年份 when 2008 then 人数 else 0 end as 人数08,
case 年份 when 2009 then 工资 else 0 end as 工资09,
case 年份 when 2008 then 工资 else 0 end as 工资08
from table
) b
) c
select
case 人数08 when 0 then 0 else (人数09-人数08)/人数08 end as 人数增长率,
case 工资08 when 0 then 0 else (工资09-工资08)/工资08 end as 工资增长率
from
(
select
sum(人数09) as 人数09,
sum(人数08) as 人数08,
sum(工资09) as 工资09,
sum(工资08) as 工资08
from
(
select
case 年份 when 2009 then 人数 else 0 end as 人数09,
case 年份 when 2008 then 人数 else 0 end as 人数08,
case 年份 when 2009 then 工资 else 0 end as 工资09,
case 年份 when 2008 then 工资 else 0 end as 工资08
from table
) b
) c
select
case 人数08 when 0 then 0 else (人数09-人数08)/人数08 end as 人数增长率,
case 工资08 when 0 then 0 else (工资09-工资08)/工资08 end as 工资增长率
from
(
select
sum(case 年份 when 2009 then 人数 else 0 end) as 人数09,
sum(case 年份 when 2008 then 人数 else 0 end) as 人数08,
sum(case 年份 when 2009 then 工资 else 0 end) as 工资09,
sum(case 年份 when 2008 then 工资 else 0 end) as 工资08
from table
) c