日期:2014-05-18  浏览次数:20433 次

100分:你敢把这个SQL语句写出来不?!
table1
user busy
-----------------------
张小一 0
张小二 1
张小三 1
张小四 0
张小五 0

table2
user busy
-----------------------
张小一 0
张小三 1
张小四 1

结果:
-----------------------
张小一 0
张小五 0

注解:
张小一,在表1和表2中busy=0所以列出
张小五,在表1中busy=0,在表2中没有该用户默认其busy=0,所以列出

张小二,在表1中busy=1,所以去除
张小三,在表1中busy=1,所以去除
张小四,虽然在表1中busy=0,但在表2中busy=1,所以去除

这样的语句该如何写呢?谢谢!

------解决方案--------------------
declare @a table([user] char(10),busy int)
insert into @a select '张小一 ',0
union all select '张小二 ',1
union all select '张小三 ',1
union all select '张小四 ',0
union all select '张小五 ',0
declare @b table([user] char(10),busy int)
insert into @b select '张小一 ',0
union all select '张小三 ',1
union all select '张小四 ',1
select * from (select * from @a where busy=0)a where [user] in(select [user] from
@b where busy=0) or [user] not in(select [user] from @b)
result:
user busy
---------- -----------
张小一 0
张小五 0

(所影响的行数为 2 行)
------解决方案--------------------
declare @table1 table([user] varchar(16),busy bigint)
insert into @table1 select '张小一 ',0
insert into @table1 select '张小二 ',1
insert into @table1 select '张小三 ',1
insert into @table1 select '张小四 ',0
insert into @table1 select '张小五 ',0

declare @table2 table([user] varchar(16),busy bigint)
insert into @table2 select '张小一 ',0
insert into @table2 select '张小三 ',1
insert into @table2 select '张小四 ',1

select * from @table1 a
where a.busy = 0
and exists (select 1 from @table2 where [user]=a.[user] and busy = 0)
union
select * from @table1 a
where a.busy = 0 and [user] not in (select [user] from @table2 b)

/*
user busy
---------------
张小五 0
张小一 0
*/