表a:
2 101 1
2 102 1
8 100 3
8 101 1
15 100 2
15 101 1
15 102 1
希望通过sql查询后形成一下形式:
2 100 0
2 101 1
2 102 1
8 100 3
8 101 1
8 102 0
15 100 2
15 101 1
15 102 1
2 101 1
2 102 1
8 100 3
8 101 1
15 100 2
15 101 1
15 102 1
希望通过sql查询后形成一下形式:
2 100 0
2 101 1
2 102 1
8 100 3
8 101 1
8 102 0
15 100 2
15 101 1
15 102 1
select c.a, c.b, nvl(d.c, 0)
from tb_wyl_test d,
(select a.a, b.b
from (select a.a from tb_wyl_test a group by a.a) a,
(select a.b from tb_wyl_test a group by a.b) b
) c
where c.a = d.a(+)
and c.b = d.b(+)
order by c.a, c.b1.根据要求,前两列必须是以最全的数据出现,所以根据前两列做笛卡尔积
2.最后一列:如果前两列在源表中没有展示为0,所以用外关联
select t1.c1 , t2.c2 , 0 c3 from
(select distinct c1 from tb) t1 cross join
(select distinct c2 from tb) t2 where not exists(select 1 from tb t3 where t3.c1 = t1.c1 and t3.c2 = t2.c2)/*
c1 c2 c3
----------- ----------- -----------
2 100 0
8 102 0(所影响的行数为 2 行)
*/
--插入
insert into tb select t1.c1 , t2.c2 , 0 c3 from
(select distinct c1 from tb) t1 cross join
(select distinct c2 from tb) t2 where not exists(select 1 from tb t3 where t3.c1 = t1.c1 and t3.c2 = t2.c2)
--不过也许这样更保险点.
select t1.c1 , t2.c2 , 0 c3 from
(select distinct c1 from tb) t1 cross join
(select 101 c2 union select 102 union select 100) t2 where not exists(select 1 from tb t3 where t3.c1 = t1.c1 and t3.c2 = t2.c2)
SELECT * FROM tab;
1 2 101 1
2 2 102 1
3 8 100 3
4 8 101 1
5 15 100 2
6 15 101 1
7 15 102 1SELECT m.id,t.seq,NVL(t.val,0)
FROM tab t
PARTITION BY (t.seq)
RIGHT JOIN
(SELECT DISTINCT ID FROM tab) m
ON m.id=t.id
ORDER BY 1,2;
oracle提供的稠化报表的一个用法。