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-1select * 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)除了这个还有没有别的好一点的方法????
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-1select * 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)除了这个还有没有别的好一点的方法????
where not exists(select 1 from tb1 where tb1.number=tb2.number and tb1.date=tb2.date)
cast(tb2.number as varchar) + cast(tb2.date as varchar) <> cast(tb1.number as varchar) + cast(tb1.date as varchar)
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 行)
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