--1.
select * from t1
where tcode not in (
select tcode from t2
union
select tcode from t3)--2.
select * from (
select tcode from t2
union
select tcode from t3) as a
where tcode not in (select tcode from t1)
select * from t1
where tcode not in (
select tcode from t2
union
select tcode from t3)--2.
select * from (
select tcode from t2
union
select tcode from t3) as a
where tcode not in (select tcode from t1)
select * from t1 a
where not exists(select 1 from (select * from t2 union all select * from t3) b where b.tcode=a.tcode)
2.
select * from (select * from t2 union all select * from t3) a
where not exists(select 1 from t1 b where b.tcode=a.tcode)
insert into @t1 select 't001'
insert into @t1 select 't002'
insert into @t1 select 't003'declare @t2 table(tcode varchar(10))
insert into @t2 select 't001'
insert into @t2 select 't001'
insert into @t2 select 't009'declare @t3 table(tcode varchar(10))
insert into @t3 select 't002'
insert into @t3 select 't002'
insert into @t3 select 't008'
--1、
------------------------------------------------------------------------------
select
a.*
from
@t1 a
where
not exists(select 1 from @t2 where tcode=a.tcode)
and
not exists(select 1 from @t3 where tcode=a.tcode)/*
tcode
----------
t003
*/
--2、
------------------------------------------------------------------------------
select
distinct a.tcode
from
(select * from @t2 union all select * from @t3) a
where
not exists(select 1 from @t1 where tcode=a.tcode)/*
tcode
----------
t008
t009
*/
tcode acode ttime
-----------------------------
t001 a001 2005-03-21 17:18:06
t002 a001 2005-03-21 17:18:23
t003 a002 2005-05-15 12:18:53
t004 a002 2005-09-12 12:18:53选出同一个acode在时间相隔1分钟之内的acode和tcode比如上面的第一行和第二行满足条件
insert into @t1 select 't001','a001','2005-03-21 17:18:06'
insert into @t1 select 't002','a001','2005-03-21 17:18:23'
insert into @t1 select 't003','a002','2005-05-15 12:18:53'
insert into @t1 select 't004','a002','2005-09-12 12:18:53'select
a.*
from
@t1 a
where
exists(select
1
from
@t1
where
tcode!=a.tcode
and
acode=a.acode
and
abs(datediff(ss,ttime,a.ttime))<=60)/*
tcode acode ttime
---------- ---------- ---------------------------
t001 a001 2005-03-21 17:18:06.000
t002 a001 2005-03-21 17:18:23.000
*/