日期:2014-05-19  浏览次数:20561 次

查不在另一张表里的记录
tb1   (Number   int,date   datetime)
Number     date
111           2003-8-9
335           2006-9-7
353           2005-8-8
111           2003-8-21
567           2006-9-9
225           2001-4-5


tb2   (Number   int,date   datetime)
Number       date
225             2006-9-3
111             2003-8-21
111             2006-12-1
904             2007-8-8

我要查出tb2中,Number在tb1中,但是Number和date不在tb1中的.
即得到        
Number         date
225               2006-9-3
111               2006-12-1

select   *   from   tb2  
where   number   in(select   distinct   number   from   tb1)
and   cast(number   as   varchar)   +   cast(date   as   varchar)   not   in
(select   cast(number   as   varchar)   +   cast(date   as   varchar)   from   tb1)

除了这个还有没有别的好一点的方法????



------解决方案--------------------
drop table tb1,tb2
go
create table tb1(Number int,date datetime)
insert into tb1
select 111, '2003-8-9 '
union all select 335, '2006-9-7 '
union all select 353, '2005-8-8 '
union all select 111, '2003-8-21 '
union all select 567, '2006-9-9 '
union all select 225, '2001-4-5 '


create table tb2(Number int,date datetime)
insert into tb2
select 225, '2006-9-3 '
union all select 111, '2003-8-21 '
union all select 111, '2006-12-1 '
union all select 904, '2007-8-8 '

select * from tb2
where not exists(select 1 from tb1 where tb1.number=tb2.number and tb1.date=tb2.date)
and exists(select 1 from tb1 where tb1.number=tb2.number)

--Number date
----------- ------------------------------------------------------
225 2006-09-03 00:00:00.000
111 2006-12-01 00:00:00.000

(所影响的行数为 2 行)

------解决方案--------------------
declare @tb1 table(Number int,[date] datetime)
insert @tb1
select 111, '2003-8-9 '
union all
select 335, '2006-9-7 '
union all
select 353, '2005-8-8 '
union all
select 111, '2003-8-21 '
union all
select 567, '2006-9-9 '
union all
select 225, '2001-4-5 '


declare @tb2 table(Number int,[date] datetime)
insert @tb2
select 225, '2006-9-3 '
union all
select 111, '2003-8-21 '
union all
select 111, '2006-12-1 '
union all
select 904, '2007-8-8 '
--==============================================
select distinct y.Number,y.[date] from @tb1 x
inner join
(
select b.Number,b.[date] from @tb1 a
right join @tb2 b
on a.Number = b.Number
and a.[date] = b.[date]
where a.number is null) y
on x.Number = y.Number