有2张表
A表:
ID MCA BHA
1 XX1 1
2 XX2 2
3 XX3 3
4 XX4 4B表:
ID MCB BHB
1 XX1 1
2 XX2 2
3 XX2 3我要的结果是
ID MCA CS
1 XX1 1
2 XX2 2
3 XX3 0
4 XX4 0
A表:
ID MCA BHA
1 XX1 1
2 XX2 2
3 XX3 3
4 XX4 4B表:
ID MCB BHB
1 XX1 1
2 XX2 2
3 XX2 3我要的结果是
ID MCA CS
1 XX1 1
2 XX2 2
3 XX3 0
4 XX4 0
from a,b
where a.id = b.id(+)
group by a.mcb
SQL> with ta as(
2 select 1 id,'XX1' mca,1 bha from dual union all
3 select 2,'XX2',2 from dual union all
4 select 3,'XX3',3 from dual union all
5 select 4,'XX4',4 from dual)
6 ,tb as(
7 select 1 id,'XX1' mcb,1 bhb from dual union all
8 select 2,'XX2',2 from dual union all
9 select 3,'XX2',3 from dual)
10 select ta.id,ta.mca,count(tb.bhb) cs
11 from ta,tb
12 where ta.mca=tb.mcb(+)
13 group by ta.id,ta.mca
14 / ID MCA CS
---------- --- ----------
1 XX1 1
2 XX2 2
3 XX3 0
4 XX4 0