SQL语句高手请进!跪求帮助!

liulinguang 2011-11-23 03:32:08
现在有两张表:Members表和Virtual表
这两张表中都有身份证字段,Members中是_id,Virtual中是_vid
有这么一个需求:
查出Members表中的所有数据,并且每条数据在输出的时候多显示一列,这一列标识该条数据中的_id与Virtual表中的_vid是否重复,也就是_id的值在表Virtual表中是否同时存在!

...全文
90 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
--小F-- 2011-11-23
select
*,(case when exists(select 1 from Virtual where [_vid] = t.[_id]) then 1 else 0 end) as flag
from
Members t
回复
leamonjxl 2011-11-23
select *,
iscf_id=(case when exists(select 1 from Virtual v where m.id=v.vid) then 1 else 0 end)
from Members m
回复
dawugui 2011-11-23
[code=SQL]select m.* , '存在' from Members m where exists(select 1 from Virtual n where m._id = n._vid)
union all
select m.* , '不存在' from Members m where not exists(select 1 from Virtual n where m._id = n._vid)

select m.* , '存在' from Members m , Virtual n where m._id = n._vid
union all
select m.* , '不存在' from Members m where not exists(select 1 from Virtual n where m._id = n._vid)

select m.* , '存在' from Members m , Virtual n where m._id = n._vid
union all
select m.* , '不存在' from Members m where _id not in (select _vid from Virtual n)
[/code]
回复
中国风 2011-11-23
select a._id,max(case when b._vid is null then 0 else 1 end)flg
from members a
left join Virtual as b on a._id=b._vi
group by a._id
回复
dawugui 2011-11-23
select m.* , '存在' from Members m , Virtual n where m._id = n._vid
union all
select m.* , '不存在' from Members m where not exists(select 1 from Virtual n where m._id = n._vid)

select m.* , '存在' from Members m , Virtual n where m._id = n._vid
union all
select m.* , '不存在' from Members m where _id not in (select _vid from Virtual n)
回复
楼上均为正解
回复
-晴天 2011-11-23
select a.*,(case when b._vid is not null then 1 else 0 end)flg
from members a left join(
select distinct _vid from Virtual)b on a._id=b._vid
回复
快溜 2011-11-23
select *,(case when exists(select 1 from Virtual where [_vid] = t.[_id]) then 1 else 0 end) flag
from Members t
回复
AcHerat 2011-11-23

select *,
(case when exists(select 1 from Virtual where [_vid] = t.[_id]) then 1 else 0 end) tFlag
from Members t
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-11-23 03:32
社区公告
暂无公告