请教一条SQL文

yinweihong 西北农林科技大学 2010-07-16 10:44:02
有2个表t1(colA,colB,colC),t2(colA,colD,colE)

我要得到这么一个结果

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

不知道一条语句能写出来不?多谢各位!


...全文
102 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
xdy3008 2010-07-20


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

回复
vber1010 2010-07-20

select t2.colA, t2.colD, decode(t1.cola, null, 0, 1) colFlg
from t2, t1
where t1.cola(+) = t2.cola
回复
oysl1122 2010-07-20
[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
回复
oysl1122 2010-07-20

select t2.colA,t2.colD,decode(t2.colA,null,0,1) colFlg
from t2
left join t1 on t1.colA=t2.colA
回复
qin_phoenix 2010-07-19

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(+)

回复
beita258 2010-07-19
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)
回复
tKF12183 2010-07-17
作一个表关联就可以实现啊。
下面代码没测试过的。。。可以试下。
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;
回复
qian_lian 2010-07-17
多表连接查询
回复
rgcsm 2010-07-17
[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
回复
rgcsm 2010-07-17
select t2.colA,t2.colD,(case when exists (select 1 from t1 where colA=t2.colA then 1 else 0 end)) colFlg from t2
回复
cyousor 2010-07-16

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

回复
yinweihong 2010-07-16
顶起来~~
回复
发动态
发帖子
基础和管理
创建于2007-09-28

1.7w+

社区成员

Oracle 基础和管理
申请成为版主
社区公告
暂无公告