表A
bh varchar(10),xm varchar(10) , zt smallint
1001 sss 0
表b
bh varchar(10),xm varchar(10) ,AreaNO smallint
1001 sss 999表C
AreaNO smallint
999
888
=====要实现的效果是=============
bh xm zt AreaNo
1001 sss 失败 999
1001 sss 成功 888==即如果B表中没有C表中AREANO,来判断A表中的ZT字段select A.bh,A.XM,
decode(A.ZT, 0,'成功' ,1,'失败') 状态 ,B.AREA
from
A A, C B (DECODE里面不知道如何构件SQL语句,达不到我的要求)
bh varchar(10),xm varchar(10) , zt smallint
1001 sss 0
表b
bh varchar(10),xm varchar(10) ,AreaNO smallint
1001 sss 999表C
AreaNO smallint
999
888
=====要实现的效果是=============
bh xm zt AreaNo
1001 sss 失败 999
1001 sss 成功 888==即如果B表中没有C表中AREANO,来判断A表中的ZT字段select A.bh,A.XM,
decode(A.ZT, 0,'成功' ,1,'失败') 状态 ,B.AREA
from
A A, C B (DECODE里面不知道如何构件SQL语句,达不到我的要求)
decode(b.b.areano
,null,decode(A.ZT, 0,'成功' ,1,'失败'),'失败') 状态
from a,b,c
where c.areano=b.areano
union
select t.bh , t.xm , '失败' zt , t.areano from (select a.bh , a.xm , c.areano from a,c) t where not exists(select 1 from b where bh = t.bh and areano = t.areano)
order by bh , xm , areano desc
b,c外联,空就是失败。
select nvl2(b.AreaNO,'失败','成功' )
from b,c
where b.AreaNO = c.AreaNO(+)
/* Formatted on 2008/05/12 17:50 (Formatter Plus v4.8.8) */
WITH a AS
(SELECT '1001' bh, 'sss' xm, 0 zt
FROM DUAL),
b AS
(SELECT '1001' bh, 'sss' xm, 999 areano
FROM DUAL),
c AS
(SELECT 999 areano
FROM DUAL
UNION ALL
SELECT 888
FROM DUAL)
SELECT a.bh, a.xm,
DECODE (b.areano,
NULL, DECODE (a.zt, 0, '成功', 1, '失败'),
'失败'
) 状态,c.areano
FROM a, b, c
WHERE c.areano = b.areano(+)
结果
Row# BH XM 状态 AREANO1 1001 sss 失败 999
2 1001 sss 成功 888