再问一个问题主表t1
tcode acode
------------------
t001 a001
t002 a001
t005 a001
从表t2(tid自增)tcode tid tnum
------------------
t001 1 45
t001 2 23
t002 3 94
t002 4 77
t003 5 65这里也有一些错误数据
1。选出t1表里有tcode而t2表里没有tcode的
(如上面主表的t005在子表里没有记录)2。选出t2表里有tcode而t1表里没有tcode的
(如上面子表的t003在主表里没有记录)
tcode acode
------------------
t001 a001
t002 a001
t005 a001
从表t2(tid自增)tcode tid tnum
------------------
t001 1 45
t001 2 23
t002 3 94
t002 4 77
t003 5 65这里也有一些错误数据
1。选出t1表里有tcode而t2表里没有tcode的
(如上面主表的t005在子表里没有记录)2。选出t2表里有tcode而t1表里没有tcode的
(如上面子表的t003在主表里没有记录)
(tcode varchar(20),acode varchar(20),ttime datetime)
insert @t
select 't001','a001','2005-11-24 11:26:17' union all
select 't002','a001','2005-11-24 11:27:49' union all
select 't003','a001','2005-11-24 11:40:05' union all
select 't004','a001','2005-11-24 11:41:05' union all
select 't005','a001','2005-11-24 11:44:05'
select * from @t where tcode in
(
select tcode from
(
select *,m=(select min(ttime) from @t a where a.tcode>b.tcode)
from @t b
) a where datediff(minute,ttime,m)<2
)
or tcode in
(
select code from
(
select ttime,m=(select min(ttime) from @t a where a.tcode>b.tcode),
code=(select min(tcode) from @t a where a.tcode>b.tcode)
from @t b
) a where datediff(minute,ttime,m)<2
)tcode acode ttime
-------------------- -------------------- ------------------------------------------------------
t001 a001 2005-11-24 11:26:17.000
t002 a001 2005-11-24 11:27:49.000
t003 a001 2005-11-24 11:40:05.000
t004 a001 2005-11-24 11:41:05.000(所影响的行数为 4 行)
(tcode varchar(20),acode varchar(20))insert @t1
select 't001','a001' union all
select 't002','a001' union all
select 't005','a001'declare @t2 table
(tcode varchar(20),tid int,tnum int)insert @t2
select 't001',1,45 union all
select 't001',2,23 union all
select 't002',3,94 union all
select 't002',4,77 union all
select 't003',5,65select tcode from @t1 a where not exists (select * from @t2 b where a.tcode=b.tcode)select tcode from @t2 a where not exists (select * from @t1 b where a.tcode=b.tcode)tcode
--------------------
t005(所影响的行数为 1 行)tcode
--------------------
t003(所影响的行数为 1 行)
-----------------------------------------------------------------------------------------------------------------------------
declare @t1 table(tcode varchar(10),acode varchar(10),ttime datetime)
insert into @t1 select 't001','a001','2005-11-24 11:26:17'
insert into @t1 select 't002','a001','2005-11-24 11:27:49'
insert into @t1 select 't003','a001','2005-11-24 11:40:05'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))<=120)/*
tcode acode ttime
---------- ---------- ------------------------------
t001 a001 2005-11-24 11:26:17.000
t002 a001 2005-11-24 11:27:49.000
*/
-----------------------------------------------------------------------------------------------------------------------------declare @t1 table(tcode varchar(10),acode varchar(10))
insert into @t1 select 't001','a001'
insert into @t1 select 't002','a001'
insert into @t1 select 't005','a001' declare @t2 table(tcode varchar(10),tid int,tnum int)
insert into @t2 select 't001',1,45
insert into @t2 select 't001',2,23
insert into @t2 select 't002',3,94
insert into @t2 select 't002',4,77
insert into @t2 select 't003',5,65
select a.* from @t1 a where not exists(select 1 from @t2 where tcode=a.tcode)/*
tcode acode
---------- ----------
t005 a001
*/
select a.* from @t2 a where not exists(select 1 from @t1 where tcode=a.tcode)/*
tcode tid tnum
---------- ----------- -----------
t003 5 65
*/