sql 表中的一个字段 排除另外一个表中的字段

[huangqq8]

表1 有time1 为时间类型，表2有time2为时间类型，将表1中的时间选出后排除表2中的时间 到月份就可以了
比如 time1 有2011-11-1、2011-12-1.time2中有2011-11-3 最后得出的结果应该是2011-12

[-晴天]

select distinct convert(varchar(7),time1,120) from 表1 a where not exists(select 1 from 表2 where convert(varchar(7),time2,120)=convert(varchar(7),a.time1,120))

[huangqing_80]

先将时间转换为字符，然后利用not exists完成

[--小F--]

select 
 convert(varchar(7),time1,120) 
from
 表1 t 
where
 not exists(select 1 from 表2 where convert(varchar(7),time2,120)=convert(varchar(7),t.time1,120))

[pengxuan]

if object_id('tb1') is not null
   drop table tb1
go
create table tb1
(
 time1 datetime
)
go
insert into tb1
select '2011-11-1' union all
select '2011-12-1'
go
if object_id('tb2') is not null
   drop table tb2
go
create table tb2
(
 time2 datetime
)
go
insert into tb2 select '2011-11-3'
go
select convert(varchar(7),time1,120) from tb1 a where not exists(select 1 from tb2 where convert(varchar(7),time2,120)=convert(varchar(7),time1,120))
go
/*
-------
2011-12

(1 行受影响)
*/

[快溜]

select
convert(varchar(7),time1,120)
from
表1 t
where
not exists(select 1 from 表2 where convert(varchar(7),time2,120)=convert(varchar(7),t.time1,120))

[唐诗三百首]

create table tab1(time1 date)
create table tab2(time2 date)

insert into tab1
select '2011-11-1' union select '2011-12-1'

insert into tab2
select '2011-11-3' 


select left(convert(varchar,a.time1,23),7) 'result'
from tab1 a
left join tab2 b
on left(convert(varchar,a.time1,23),7)=
left(convert(varchar,b.time2,23),7)
where b.time2 is null

result
--------------
2011-12

(1 row(s) affected)

[huangqq8]

谢谢大家