Question

oracle遍历并修改数据

现有表A

BASE_DT PRO_NO PRONM SALE_AMT
20150420 L1 鞋类 5000
20150420 L2 食品类 15000
20150520 L1 鞋类 8000
20150520 L2 食品类 25000
20150520 L3 电器类 55000
20150620 L1 鞋类 8000
我想取到的数据是
20150420 L1 鞋类 5000
20150420 L2 食品类 15000
20150420 L3 电器类 0
20150520 L1 鞋类 8000
20150520 L2 食品类 25000
20150520 L3 电器类 55000
20150620 L1 鞋类 8000
20150620 L2 食品类 0
20150620 L3 电器类 0

如何遍历该表并向上面一样，当该日期种类不全的时候赋默认值，
请拯救我这个菜鸟吧，刚接触ORACLE 没有分了~~跪谢~！

Answer 1

SELECT NVL(B.BASE_DT, A.BASE_DT) BASE_DT,
A.PRO_NO,
A.PRONM,
NVL(SALE_AMT, 0)
FROM (SELECT T.BASE_DT, A.PRO_NO, PRONM
FROM (SELECT DISTINCT BASE_DT FROM SHOPS) T,
(SELECT DISTINCT PRO_NO, PRONM FROM SHOPS) A) A,
(SELECT * FROM SHOPS) B
WHERE A.PRO_NO = B.PRO_NO(+)
AND A.BASE_DT = B.BASE_DT(+)
ORDER BY NVL(B.BASE_DT, A.BASE_DT), PRO_NO;

Answer 2

select * from table order by BASE_DT asc

追问

请看清楚要求再解答好嘛