SQL> select a.bh, a.mc, decode(b.id,null,0,1)as flag from test_a a left join test_b b on a.bh = b.bh 2 order by a.bh 3 ;BH MC FLAG -- -- ---------- 01 a 0 02 b 1 03 c 1 04 d 0 05 e 1
楼主的是oracle9i或以上的版本??是的话用这个: select a.bh,a.mc,decode(nvl(b.bh,-1),-1,0,1) flag from a RIGHT OUTER JOIN b ON a.bh=b.bh
SQL> select * from test_b;BH ID -- --- 02 001 03 001 05 001 05 002 04 003SQL> SQL> select a.bh, a.mc, decode(b.id,null,0,1)as flag from test_a a left join test_b b on a.bh = b.bh and b.id = '001' 2 order by a.bh 3 ;BH MC FLAG -- -- ---------- 01 a 0 02 b 1 03 c 1 04 d 0 05 e 1
2 order by a.bh
3 ;BH MC FLAG
-- -- ----------
01 a 0
02 b 1
03 c 1
04 d 0
05 e 1
select a.bh,a.mc,decode(nvl(b.bh,-1),-1,0,1) flag
from a RIGHT OUTER JOIN b ON a.bh=b.bh
-- ---
02 001
03 001
05 001
05 002
04 003SQL>
SQL> select a.bh, a.mc, decode(b.id,null,0,1)as flag from test_a a left join test_b b on a.bh = b.bh and b.id = '001'
2 order by a.bh
3 ;BH MC FLAG
-- -- ----------
01 a 0
02 b 1
03 c 1
04 d 0
05 e 1