求一条sql语句，急 急 急
表#：
id household_id username name
1 8 zjh aa
2 8 zjh bb
3 8 zjh cc
4 9 xl dd
5 9 xl ee
6 9 xl ff
表#1：
id household_id username name
1 8 zjh aa
2 8 zjh bb
要求得到的结果为：
id household_id username name
3 8 zjh cc
要求：在表#中选择出与表#1中username，household_id 相同 ,id 不同的数据！
create table #( id int identity,household_id int ,username varchar(10),name varchar(10))
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 b where a.username=b.username and a.household_id=b.household_id and a.id <> b.id)
[解决办法]
create table #( id int identity,household_id int ,username varchar(10),name varchar(10))
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 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)
[解决办法]
SELECT * FROM # WHERE ID NOT IN (SELECT ID FROM #1) AND #.username IN (SELECT username FROM #1) AND #.household_id IN (SELECT household_id FROM #1)
