形如A表:
ID B_NO TOTAL
1 4001 3
2 4001 3
3 4001 5
4 4001 7
2 4021 4
3 4033 9
通过select distinct ID from A 可以得到ID值有1、2、3、4四种;
通过select distinct B_NO from A 可以得到B_NO值有4001、4021、4033三种;
现在想通过怎么操作,实现查询结果如下:
ID B_NO TOTAL
1 4001 3
2 4001 3
3 4001 5
4 4001 71 4021 0
2 4021 4
3 4021 0
4 4021 01 4033 0
2 4033 0
3 4033 9
4 4033 0
即对4021、4033按照ID扩展,TOTAL填值为0。
ID B_NO TOTAL
1 4001 3
2 4001 3
3 4001 5
4 4001 7
2 4021 4
3 4033 9
通过select distinct ID from A 可以得到ID值有1、2、3、4四种;
通过select distinct B_NO from A 可以得到B_NO值有4001、4021、4033三种;
现在想通过怎么操作,实现查询结果如下:
ID B_NO TOTAL
1 4001 3
2 4001 3
3 4001 5
4 4001 71 4021 0
2 4021 4
3 4021 0
4 4021 01 4033 0
2 4033 0
3 4033 9
4 4033 0
即对4021、4033按照ID扩展,TOTAL填值为0。
select 1 id, 4001 b_no, 3 total from dual union all
select 2 id, 4001 b_no, 3 total from dual union all
select 3 id, 4001 b_no, 5 total from dual union all
select 4 id, 4001 b_no, 7 total from dual union all
select 2 id, 4021 b_no, 4 total from dual union all
select 3 id, 4033 b_no, 9 total from dual)SELECT c.id, c.b_no, nvl(t.total, 0)
FROM (SELECT a.id, b.b_no
FROM (SELECT DISTINCT id FROM t) a,
(SELECT DISTINCT b_no FROM t) b) c, t
WHERE c.id = t.id(+)
AND c.b_no = t.b_no(+)
ORDER BY c.b_no, c.id;
SELECT t2.id,
t1.b_no,
NVL(t2.total,0)
FROM (SELECT DISTINCT b_no
FROM t) t1
LEFT JOIN t t2 PARTITION BY(t2.ID) ON t1.b_no = t2.b_no
ORDER BY 2,1