请教一条SQL文

[yinweihong]

有2个表t1(colA,colB,colC),t2(colA,colD,colE)

我要得到这么一个结果

分二步很好实现
1.select colA,colD,colFlg from t2
2.第一步得到的记录集，每条记录拿colA到t1表里面查询，如果存在则把对应记录的colFlg设置为1否则设置为0

不知道一条语句能写出来不？多谢各位！

[yinweihong]

顶起来～～

[cyousor]

With t1 As(
Select 1 colA, 'a' colB, 'a' colC From dual 
Union All Select 2,'b','b' From dual
Union All Select 3,'c','c' From dual)
,t2 As(
Select 1 colA, 'a' colD, 'a' colE From dual 
Union All Select 4,'b','b' From dual
)
Select Cola,
       Cold,
       Cole,
       (Case
         When (Select Count(1) From T1 Where T1.Cola = T2.Cola) > 0 Then
          1
         Else
          0
       End) Colflag
  From T2

[rgcsm]

select t2.colA,t2.colD,(case when exists (select 1 from t1 where colA=t2.colA then 1 else 0 end)) colFlg from t2

[rgcsm]

[Quote=引用 4 楼 rgcsm 的回复:]
select t2.colA,t2.colD,(case when exists (select 1 from t1 where colA=t2.colA then 1 else 0 end)) colFlg from t2
[/Quote]
不好意思括号错了。
select t2.colA,t2.colD,(case when exists (select 1 from t1 where colA=t2.colA) then 1 else 0 end) colFlg from t2

[qian_lian]

多表连接查询

[tKF12183]

作一个表关联就可以实现啊。
下面代码没测试过的。。。可以试下。
select b.colA colA,b.colD colD,case when a.cola is null 0 else 1 end colflg from t2 b left join t1 a on b.cola=a.cola;

[beita258]

update t2
set t2.colFlg = (select 1 from t1,t2 where t1.cola = t2.cola)
where exists ( select 1 from t1,t2 where t1.cola = t2.cola)

[qin_phoenix]

WITH t1 AS(
    SELECT 1 COLA, 'a' COLB, 'a' COLC
      FROM DUAL
    UNION ALL
    SELECT 2, 'b', 'b'
      FROM DUAL
    UNION ALL
    SELECT 3, 'c', 'c' FROM DUAL)
,t2 AS(
    SELECT 1 COLA, 'a' COLD, 'a' COLE
      FROM DUAL
    UNION ALL
    SELECT 4, 'b', 'b' FROM DUAL
)
SELECT T2.COLA, T2.COLD, T2.COLE, NVL2(T1.COLA, 1, 0)
  FROM T1, T2
 WHERE T2.COLA = T1.COLA(+)

[oysl1122]

select t2.colA,t2.colD,decode(t2.colA,null,0,1) colFlg
from t2
left join t1 on t1.colA=t2.colA

[oysl1122]

[Quote=引用 9 楼 oysl1122 的回复:]
select t2.colA,t2.colD,decode(t2.colA,null,0,1) colFlg
from t2
left join t1 on t1.colA=t2.colA
[/Quote]


select t2.colA,t2.colD,decode(t1.colA,null,0,1) colFlg
from t2
left join t1 on t1.colA=t2.colA

[vber1010]

select t2.colA, t2.colD, decode(t1.cola, null, 0, 1) colFlg
  from t2, t1
 where t1.cola(+) = t2.cola

[xdy3008]

WITH t1 AS(
    SELECT 1 COLA, 'a' COLB, 'a' COLC
      FROM DUAL
    UNION ALL
    SELECT 2, 'b', 'b'
      FROM DUAL
    UNION ALL
    SELECT 3, 'c', 'c' FROM DUAL)
,t2 AS(
    SELECT 1 COLA, 'a' COLD, 'a' COLE
      FROM DUAL
    UNION ALL
    SELECT 4, 'b', 'b' FROM DUAL
)
SELECT T2.COLA, T2.COLD, T2.COLE, NVL2(T1.COLA, 1, 0)
  FROM T1 right outer join T2 on 
T2.COLA = T1.COLA