动态追加日累计、月累计:
- 测试表tb_test_over 中有SCRQ、RSJ两列,存储每日的日数据;
select * from tb_test_over ;
- 使用
OVER
函数动态追加列,根据SCRQ对RSJ累计求和;
select scrq,rsj,
sum(rsj) over(order by scrq) as nsj
from tb_test_over order by scrq;
- 使用
OVER
结合PARTITION BY
对每月进行累加;
select scrq,rsj,sum(rsj)
over(partition by to_char(scrq,'yyyy-mm') order by scrq) ysj
from tb_test_over
order by scrq;
- 综合版,查询日、月、年累加数据;
select scrq,rsj,
sum(rsj) over(partition by to_char(scrq,'yyyy-mm') order by scrq) ysj,
sum(rsj) over(order by scrq) as nsj
from tb_test_over
order by scrq;
5、最后追加一个动态字符列转行技巧;
有'A,B,V,C,D,S,A'
这么一个字符串,根据逗号,
将数据输出到每行;
SELECT REGEXP_SUBSTR ('A,B,V,C,D,S,A', '[^,]+', 1,ROWNUM) as str
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH ('A,B,V,C,D,S,A') - LENGTH (REPLACE ('A,B,V,C,D,S,A', ',', ''))+1;
转载:https://blog.csdn.net/u012324101/article/details/80251003