17,078
社区成员
发帖
与我相关
我的任务
分享
表M数据如下:
SEQ_NO | TYPE_NO | MONEY |
A | 1 | 10 |
A | 2 | 20 |
B | 5 | 30 |
表N数据如下:
TYPE_NO |
1 |
2 |
3 |
4 |
5 |
M表和N表通过type_no关联,想得到以下结果:
SEQ_NO | TYPE_NO | MONEY |
A | 1 | 10 |
A | 2 | 20 |
A | 3 | 0 |
A | 4 | 0 |
A | 5 | 0 |
B | 1 | 0 |
B | 2 | 0 |
B | 3 | 0 |
B | 4 | 0 |
B | 5 | 30 |
select a.a,n.b,nvl(m.c,0)
from (select distinct a from m) a
full join n on 1=1
full join m on a.a=m.a and n.b=m.b