select * from # a
where exists(select 1 from #1 b where a.username=b.username and a.household_id=b.household_id and a.id<>b.id)
where exists(select 1 from #1 b where a.username=b.username and a.household_id=b.household_id and a.id<>b.id)
insert into #(household_id,username,name) select '8','zjh','aa'
insert into #(household_id,username,name) select '8','zjh','bb'
insert into #(household_id,username,name) select '8','zjh','cc'
insert into #(household_id,username,name) select '9','xl','dd'
insert into #(household_id,username,name) select '9','xl','ee'
insert into #(household_id,username,name) select '9','xl','ff'
create table #1( id int ,household_id int ,username varchar(10),name varchar(10))
insert into #1(id, household_id,username,name) select 1, '8','zjh','aa'
insert into #1(id,household_id,username,name) select 2, '8','zjh','bb'select
*
from
# a
where
exists(select 1 from #1 where household_id=a.household_id)
and
not exists(select 1 from #1 where household_id=a.household_id and id=a.id)drop table #,#1
select * from # a
where exists(select 1 from #1 b where a.username=b.username and a.household_id=b.household_id and a.id<>b.id)我是这样写的,结果还是可以查出来,只是代码冗余比较多,望各位大侠帮忙,分少可以再补!select a.* from # a,#1 b
where a.household_id=b.household_id and a.username=b.username and a.id not in(select a.id from # a, #1 b
where a.id=b.id and a.household_id=b.household_id and a.username=b.username)
where
household_id in (select distinct household_id from #1)
and
id not in (select distinct id from #1)
楼上的两位都错啦,还是libin_ftsafe(子陌红尘)厉害:呵呵!
*
from
# a
where
exists(select 1 from #1 where household_id=a.household_id and username=a.username)
and
not exists(select 1 from #1 where id=a.id and household_id=a.household_id and username=a.username)
*
from
# a
where
exists(select 1 from #1 where household_id=a.household_id and username=a.username )
and
not exists(select 1 from #1 where household_id=a.household_id and username=a.username and name=a.name)