17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> WITH A AS (
2 SELECT 123 ID,'01' 月份,100 金额 FROM DUAL UNION ALL
3 SELECT 123 ID,'02' 月份,200 金额 FROM DUAL UNION ALL
4 SELECT 123 ID,'03' 月份,400 金额 FROM DUAL UNION ALL
5 SELECT 321 ID,'12' 月份,200 金额 FROM DUAL
6 ),B AS (
7 SELECT 01 月份 FROM DUAL UNION ALL
8 SELECT 02 月份 FROM DUAL UNION ALL
9 SELECT 03 月份 FROM DUAL UNION ALL
10 SELECT 04 月份 FROM DUAL UNION ALL
11 SELECT 05 月份 FROM DUAL UNION ALL
12 SELECT 06 月份 FROM DUAL UNION ALL
13 SELECT 07 月份 FROM DUAL UNION ALL
14 SELECT 08 月份 FROM DUAL UNION ALL
15 SELECT 09 月份 FROM DUAL UNION ALL
16 SELECT 10 月份 FROM DUAL UNION ALL
17 SELECT 11 月份 FROM DUAL UNION ALL
18 SELECT 12 月份 FROM DUAL
19 )
20 SELECT T.ID,B.月份,nvl(A.金额,0) 金额
21 FROM (
22 select distinct ID from A) T JOIN B
23 ON 1=1
24 LEFT JOIN A
25 ON T.ID=A.ID AND B.月份=A.月份
26 order by T.ID,B.月份;
ID 月份 金额
---------- ---------- ----------
123 1 100
123 2 200
123 3 400
123 4 0
123 5 0
123 6 0
123 7 0
123 8 0
123 9 0
123 10 0
123 11 0
ID 月份 金额
---------- ---------- ----------
123 12 0
321 1 0
321 2 0
321 3 0
321 4 0
321 5 0
321 6 0
321 7 0
321 8 0
321 9 0
321 10 0
ID 月份 金额
---------- ---------- ----------
321 11 0
321 12 200
已选择24行。
SQL>
SELECT T.ID,B.月份,nvl(A.金额,0) 金额
FROM (
select distinct ID from A) T JOIN B
LEFT JOIN A
ON T.ID=A.ID AND B.月份=A.月份