也可以看另一个贴 http://topic.csdn.net/u/20080426/16/a3560817-548c-4eeb-9397-17230772d725.html?seed=2099815257表
DIVISION_ID NATION_ID ZDRS WEEKNUM
海淀 3 34 6
海淀 2 33 6
海淀 1 22 6
昌平 3 44 6
昌平 2 32 6
昌平 1 54 6
西城 1 42 6
西城 2 44 6
海淀 1 45 5
海淀 2 55 5
海淀 3 176 5
昌平 3 233 5
昌平 2 4 5
西城 2 6 5
西城 1 73 5
==============================================
可以建表
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
==================================================================
在weeknum 列 表示周数 即为第几周 weeknum中最大的为本周,weeknum-1为上周,weeknum-4为上月, 我想写sql语句 实现查本周数据,上周数据。 然后本周与上周比 即 本周/上周
我这样写:
select DIVISION_ID,
NATION_ID,
sum(case WEEKNUM when max(weeknum)-1 then ZDRS else 0 end) [上周],
sum(case WEEKNUM when max(weeknum) then ZDRS else 0 end) [本周]
from tb
group by DIVISION_ID,NATION_ID
order by DIVISION_ID,NATION_ID ==========================================================
不能对包含聚合或子查询的表达式执行聚合函数。
该怎么实现? 本周/上周怎么实现??
谢谢!!!
DIVISION_ID NATION_ID ZDRS WEEKNUM
海淀 3 34 6
海淀 2 33 6
海淀 1 22 6
昌平 3 44 6
昌平 2 32 6
昌平 1 54 6
西城 1 42 6
西城 2 44 6
海淀 1 45 5
海淀 2 55 5
海淀 3 176 5
昌平 3 233 5
昌平 2 4 5
西城 2 6 5
西城 1 73 5
==============================================
可以建表
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
==================================================================
在weeknum 列 表示周数 即为第几周 weeknum中最大的为本周,weeknum-1为上周,weeknum-4为上月, 我想写sql语句 实现查本周数据,上周数据。 然后本周与上周比 即 本周/上周
我这样写:
select DIVISION_ID,
NATION_ID,
sum(case WEEKNUM when max(weeknum)-1 then ZDRS else 0 end) [上周],
sum(case WEEKNUM when max(weeknum) then ZDRS else 0 end) [本周]
from tb
group by DIVISION_ID,NATION_ID
order by DIVISION_ID,NATION_ID ==========================================================
不能对包含聚合或子查询的表达式执行聚合函数。
该怎么实现? 本周/上周怎么实现??
谢谢!!!
select DIVISION_ID,NATION_ID,WEEKNUM ,sum(zdrs) sum_zdrs,
lag(sum(zdrs),1,0) over(partition DIVISION_ID,NATION_ID order by weeknum) pre_sum_zdrs,
row_number() over(partition DIVISION_ID,NATION_ID order by weeknum desc) rn
from tb
group by DIVISION_ID,NATION_ID,WEEKNUM ) a
where aa.rn=1
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排了下版,修正了两个分析函数少写了BY的错误
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