表a:
id
1
2
3表b:
id day content
1 1 1
1 2 1
2 1 1
2 3 1想要如下结果:
id day content
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1
3 1 0
3 2 0
3 3 0对您确实不难,不过请您试过成功之后再回答,ok?
id
1
2
3表b:
id day content
1 1 1
1 2 1
2 1 1
2 3 1想要如下结果:
id day content
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 1
3 1 0
3 2 0
3 3 0对您确实不难,不过请您试过成功之后再回答,ok?
id day content
1 1 1
1 2 1
1 3 0
2 1 1
2 2 0
2 3 1
3 1 0
3 2 0
3 3 0
----------
1
2
3SQL> select * from c; A B C
---------- ---------- ----------
1 1 1
1 2 1
2 1 1
2 3 1SQL> select n.a,n.b,nvl(c.c,0) from (select b.a,m.b from b,(select distinct b fr
om c) m) n,c where n.a=c.a(+) and n.b=c.b(+) order by n.a,n.b; A B NVL(C.C,0)
---------- ---------- ----------
1 1 1
1 2 1
1 3 0
2 1 1
2 2 0
2 3 1
3 1 0
3 2 0
3 3 0已选择9行。SQL>
sql如下:
SELECT base.id, base.DAY, nvl(b.content, 0)
FROM (SELECT * FROM a, (SELECT DISTINCT DAY FROM b)) base
LEFT JOIN b ON b.id = base.id
AND base.DAY = b.DAY
ORDER BY base.id, base.DAY