表A字段如下
month person income
月份 人员 收入
要求用一个SQL语句(注意是一个)的处所有人(不区分人员)每个月及上月和下月的总收入
要求列表输出为
月份 当月收入 上月收入 下月收入
month person income
月份 人员 收入
要求用一个SQL语句(注意是一个)的处所有人(不区分人员)每个月及上月和下月的总收入
要求列表输出为
月份 当月收入 上月收入 下月收入
调试欢乐多
from tb A left join tb B on A.moth=B.month-1 right join C on a.moth=C.month+1
create table #t (month int,person varchar(10),income int)insert into #t
select 1,'张三',2300 union all
select 1,'李四',3000 union all
select 1,'王二',1800 union all
select 2,'张三',1300 union all
select 2,'李四',3020 union all
select 2,'王二',1850 union all
select 3,'张三',2380 union all
select 3,'李四',3100 union all
select 3,'王二',3000 union all
select 4,'张三',1900 union all
select 4,'李四',3300 union all
select 4,'王保',1800--公司(所有人)月合计报表select month,sum(income) as income
into #t1
from #t
group by month select cur.month,sum(cur.income) as cur_income,sum(pre.income) as pre_income,sum(nex.income) as nex_income
from #t1 cur left join #t1 pre on cur.month=pre.month +1 left join #t1 nex on cur.month=nex.month-1
group by cur.month
order by cur.month--个人月合计报表
select month,person,sum(income) as income
into #t2
from #t
group by month,person select cur.person,cur.month,sum(cur.income) as cur_income,sum(pre.income) as pre_income,sum(nex.income) as nex_income
from #t2 cur left join #t2 pre on cur.person=pre.person and cur.month=pre.month +1 left join #t2 nex on cur.person=nex.person and cur.month=nex.month-1
group by cur.month,cur.person
order by cur.person,cur.month
drop table #t1
drop table #t2
drop table #t
结果如下:
month cur_income pre_income nex_income
----------- ----------- ----------- -----------
1 7100 NULL 6170
2 6170 7100 8480
3 8480 6170 7000
4 7000 8480 NULL(所影响的行数为 4 行)
person month cur_income pre_income nex_income
---------- ----------- ----------- ----------- -----------
李四 1 3000 NULL 3020
李四 2 3020 3000 3100
李四 3 3100 3020 3300
李四 4 3300 3100 NULL
王保 4 1800 NULL NULL
王二 1 1800 NULL 1850
王二 2 1850 1800 3000
王二 3 3000 1850 NULL
张三 1 2300 NULL 1300
张三 2 1300 2300 2380
张三 3 2380 1300 1900
张三 4 1900 2380 NULL
月份=datepart(month,a.月份),
当月收入=SUM(a.收入),
上月收入=SUM(b.收入),
下月收入=SUM(c.收入)
from tb a join tb b on datepart(month,a.月份)=datepart(month,b.月份)+1
join tb c on datepart(month,a.月份)=datepart(month,c.月份)-1
group by datepart(month,a.月份)