假设我有个Table C,内容如下
date name value
200707 AA 50
200707 AA 100
200707 BB 100
200708 AA 200
200708 BB 200
200709 AA 300
200709 BB 300我现要统计成如下结果:
date name curValue toatlValue
200707 AA 100 150
200707 BB 100 100
200708 AA 200 350
200708 BB 200 300
200709 AA 300 650
200709 BB 300 600也就是说curValue 是当前月份的统计 ,toatlValue是所有月份的累加值,求一条统计语句
date name value
200707 AA 50
200707 AA 100
200707 BB 100
200708 AA 200
200708 BB 200
200709 AA 300
200709 BB 300我现要统计成如下结果:
date name curValue toatlValue
200707 AA 100 150
200707 BB 100 100
200708 AA 200 350
200708 BB 200 300
200709 AA 300 650
200709 BB 300 600也就是说curValue 是当前月份的统计 ,toatlValue是所有月份的累加值,求一条统计语句
200707 AA 100
有两个,是不是取最大值?
不知道行不行,你试试看
date name value
200707 AA 50
200707 AA 100
200707 BB 100
200708 AA 200
200708 BB 200
200709 AA 300
200709 BB 300我现要统计成如下结果:
date name curValue toatlValue
200707 AA 150 150
200707 BB 100 100
200708 AA 200 350
200708 BB 200 300
200709 AA 300 650
200709 BB 300 600
insert into table1 values('200707','AA',50);
insert into table1 values('200707','AA',100);
insert into table1 values('200707','BB',100);
insert into table1 values('200708','AA',200);
insert into table1 values('200708','BB',200);
insert into table1 values('200709','AA',300);
insert into table1 values('200709','BB',300);
commit;select date1,name,sum(curValue),max(toatlValue) from
(select date1,name,value curValue,
sum(value) over(Partition By name order by date1,name) toatlValue
from table1 order by date1,name,value
)
group by date1,name;DATE1 NAME SUM(CURVALUE) MAX(TOATLVALUE)
---------- ---------- ------------- ---------------
200707 AA 150 150
200707 BB 100 100
200708 AA 200 350
200708 BB 200 300
200709 AA 300 650
200709 BB 300 600