with t as
(select 201401 as month_id, 1 as region_id, 10 as cal_data
from dual
union
select 201401, 2, 20
from dual
union
select 201402, 1, 30
from dual
union
select 201403, 1, 40
from dual)
select month_id,
region_id,
cal_data,
sum(cal_data) over(partition by region_id, substr(month_id, 1, 4) order by month_id) lj
from (select tt1.month_id, tt1.region_id, nvl(tt2.cal_data, 0) cal_data
from (select month_id, region_id
from (select distinct month_id from t) t1,
(select distinct region_id from t) t2) tt1,
t tt2
where tt1.month_id = tt2.month_id(+)
and tt1.region_id = tt2.region_id(+))
解决方案 »
- 请教一个sql语句
- 收集Oracle ==> SQL server 2005数据迁移问题解决方案
- 求助:Oracle中的批处理命令是什么?(就像SQL中GO一样功能的命令,我在网上找了半天,没有找到精确的答案,各位大虾,救命!!!)
- 能够使用其他SQL替代外连接吗?Help。。。。
- 日期时间段的sql,怎么写?
- 求sql语句
- 上亿条记录,表的设计问题,请教!!!!!
- 请教个问题!关于汉字查询!
- 数据库查询中,如果某个连接查询,查询计划是嵌套查询,那么外部循环是大表好还是小表好?
- 请教大家一个Oracle子查询更新表的问题:无法修改与非键值保存表对应的列
- 求一sql,key value 列查询的
- 请教开发用oracle的一些问题
select month_id, region_id, cal_data, sum(cal_data) over(partition by region_id,substr(month_id,1, 4)
order by month_id) lj
from (
SELECT month_id, region_id, cal_data FROM T
UNION ALL
SELECT month_id, 2, 0 FROM T T1
WHERE NOT EXISTS(SELECT 1 FROM T WHERE month_id=T1.month_id AND region_id=2)
)