17,382
社区成员




可以建表
create table tb(DIVISION_ID varchar(20),NATION_ID int,ZDRS int,WEEKNUM int)
insert into tb values('海淀',3,34,6)
insert into tb values('海淀',2,33,6)
insert into tb values('海淀',1,32,6)
insert into tb values('昌平',3,44,6)
insert into tb values('昌平',2,32,6)
insert into tb values('昌平',1,54,6)
insert into tb values('西城',1,42,6)
insert into tb values('西城',2,44,6)
insert into tb values('海淀',1,45,5)
insert into tb values('海淀',2,55,5)
insert into tb values('海淀',3,176,5)
insert into tb values('昌平',3,233,5)
insert into tb values('昌平',2,4,5)
insert into tb values('西城',2,6,5)
insert into tb values('西城',1,73,5)
go
--取本周
declare @this_week int
select @this_week = max(weeknum) from tb;
--计算本周/上周
select DIVISION_ID,
NATION_ID,
sum(case WEEKNUM when @this_week - 1 then ZDRS else 0 end) [上周],
sum(case WEEKNUM when @this_week then ZDRS else 0 end) [本周]
from tb
group by DIVISION_ID,NATION_ID
order by DIVISION_ID,NATION_ID
SELECT a division_id, nation_id, aa.sum_zdrs "本周", aa.pre_sum_zdrs "上周",
DECODE (aa.pre_sum_zdrs,
0, NULL,
aa.sum_zdrs / aa.pre_sum_zdrs
) "本周/上周"
FROM (SELECT division_id, nation_id, weeknum, SUM (zdrs) sum_zdrs,
LAG (SUM (zdrs), 1, 0) OVER (PARTITION BY division_id, nation_id ORDER BY weeknum)
pre_sum_zdrs,
ROW_NUMBER () OVER (PARTITION BY division_id, nation_id ORDER BY weeknum DESC)
rn
FROM tb
GROUP BY division_id, nation_id, weeknum) a
WHERE aa.rn = 1