日期:2014-05-17 浏览次数:20506 次
create table #ta(姓名 varchar(10),低保证号 varchar(10))
insert into #ta
select '张三','001'
union all select '李四','002'
union all select '李七','002'
union all select '王五','003'
union all select '赵九','004'
union all select '杨十','002'
union all select '杨二','005'
create table #tb(姓名 varchar(10),成员 varchar(10),低保证号 varchar(10))
insert into #tb
select '张三','张三','001'
union all select '张三','张六','001'
union all select '李四','李四','002'
union all select '李四','李七','002'
union all select '王五','王五','003'
union all select '王五','王八','003'
union all select '杨一','杨一','005'
union all select '杨一','杨二','005'
--问题1.
select *
from #ta a
where not exists(select 1 from #tb b where a.低保证号=b.低保证号)
/*
赵九 004
*/
--问题2.
select *
from #ta a
where not exists(select 1 from #tb b where a.低保证号=b.低保证号 and a.姓名=b.成员)