to swordmanli(乘风破浪会有时,直挂云帆济沧海--太白) : 你这是什么语句!!!这样也成......
Select a.*,r.* From autotest a Inner Join repair r on a.SSN=r.SSN and a.station<>r.station where dateAdd(hour,16,a.testdate)<>r.createtime
select autotest.* from autotest,repair where dateadd(minute,16, autotest.testdate)<>repair.createtime and autotest.ssn=repair.ssn and autotest.station<>repair.station
--什么是'共同的SSN'? --问题没有明白
--两个表中的station不相同的记录。 那怎么会有2和8?
"共同的SSN" 就是说这两个表都有的SSN."两个表中的station不相同的记录"应该是多余的。小弟需要的是autotest.SSN=repair.ssn and dateadd(minute,16, autotest.testdate)<>repair.createtime 的记录。但不能有ssn,testdate这两个条件一样的记录。
但不能有ssn,testdate这两个条件一样的二笔想同的记录。 为什么没有问答啊。各位大大帮忙啊
不能有ssn,testdate这两个条件一样的记录是什么意思?
--按条件 --autotest.SSN=repair.ssn and dateadd(minute,16, autotest.testdate)<>repair.createtime 的记录select a.* from autotest a where exists( select 1 from repair where ssn=a.ssn and dateadd(minute,16,a.testdate)<>createtime)--结果: id ssn testdate station ----------- ---------- --------------------------- ---------- 1 J0145A 2004-07-01 14:20:30.000 FA 2 J0145A 2004-07-01 15:45:30.000 FA 3 J0145A 2004-07-02 04:15:46.000 FB 4 J0145A 2004-07-03 12:45:21.000 FB 5 J0145B 2004-07-03 05:45:23.000 FB 6 J0145B 2004-07-05 04:45:25.000 FA 7 J0145B 2004-07-06 13:52:12.000 FA 8 J0145C 2004-07-08 16:53:56.000 FB 9 J0145C 2004-07-12 22:12:45.000 FB(所影响的行数为 9 行)
create table autotest (ssn nvarchar(10), testdate datetime, station nvarchar(10)) insert into autotest select 'J0145A', '2004-07-01 14:20:30' ,'FA' union all select 'J0145A' , '2004-07-01 15:45:30' , 'FA' union all select 'J0145A' , '2004-07-02 04:15:46' , 'FB' union all select 'J0145A' , '2004-07-03 12:45:21' , 'FB' union all select 'J0145B' , '2004-07-03 05:45:23' , 'FB' union all select 'J0145B' , '2004-07-05 04:45:25' , 'FA' union all select 'J0145B' , '2004-07-06 13:52:12' , 'FA' union all select 'J0145C' , '2004-07-08 16:53:56' , 'FB' union all select 'J0145C' , '2004-07-12 22:12:45 ', 'FB'create table repair(ssn nvarchar(10), createtime datetime, station nvarchar(10)) insert into repair select 'J0145A', '2004-07-02 06:20:30', 'FA' union all select 'J0145A' , '2004-07-04 04:45:21' , 'FB' union all select 'J0145B' , '2004-07-03 21:45:23' , 'FB' union all select 'J0145C' , '2004-07-13 14:12:45' , 'FB' select * from autotest O where exists(select 1 from repair where ssn=O.ssn and dateAdd(hour,16,O.testdate)<>createtime) and not exists(select 1 from repair where ssn=O.ssn and station=O.station and dateAdd(hour,16,O.testdate)=createtime) /* J0145A 2004-07-01 15:45:30.000 FA J0145A 2004-07-02 04:15:46.000 FB J0145B 2004-07-05 04:45:25.000 FA J0145B 2004-07-06 13:52:12.000 FA J0145C 2004-07-08 16:53:56.000 FB*/
From autotest a Inner Join repair r
on a.SSN=r.SSN and a.station<>r.station
where dateAdd(hour,16,a.testdate)<>r.createtime
--问题没有明白
那怎么会有2和8?
为什么没有问答啊。各位大大帮忙啊
--autotest.SSN=repair.ssn and dateadd(minute,16, autotest.testdate)<>repair.createtime 的记录select a.*
from autotest a
where exists(
select 1 from repair
where ssn=a.ssn
and dateadd(minute,16,a.testdate)<>createtime)--结果:
id ssn testdate station
----------- ---------- --------------------------- ----------
1 J0145A 2004-07-01 14:20:30.000 FA
2 J0145A 2004-07-01 15:45:30.000 FA
3 J0145A 2004-07-02 04:15:46.000 FB
4 J0145A 2004-07-03 12:45:21.000 FB
5 J0145B 2004-07-03 05:45:23.000 FB
6 J0145B 2004-07-05 04:45:25.000 FA
7 J0145B 2004-07-06 13:52:12.000 FA
8 J0145C 2004-07-08 16:53:56.000 FB
9 J0145C 2004-07-12 22:12:45.000 FB(所影响的行数为 9 行)
insert into autotest
select 'J0145A', '2004-07-01 14:20:30' ,'FA'
union all select 'J0145A' , '2004-07-01 15:45:30' , 'FA'
union all select 'J0145A' , '2004-07-02 04:15:46' , 'FB'
union all select 'J0145A' , '2004-07-03 12:45:21' , 'FB'
union all select 'J0145B' , '2004-07-03 05:45:23' , 'FB'
union all select 'J0145B' , '2004-07-05 04:45:25' , 'FA'
union all select 'J0145B' , '2004-07-06 13:52:12' , 'FA'
union all select 'J0145C' , '2004-07-08 16:53:56' , 'FB'
union all select 'J0145C' , '2004-07-12 22:12:45 ', 'FB'create table repair(ssn nvarchar(10), createtime datetime, station nvarchar(10))
insert into repair
select 'J0145A', '2004-07-02 06:20:30', 'FA'
union all select 'J0145A' , '2004-07-04 04:45:21' , 'FB'
union all select 'J0145B' , '2004-07-03 21:45:23' , 'FB'
union all select 'J0145C' , '2004-07-13 14:12:45' , 'FB'
select * from autotest O
where exists(select 1 from repair where ssn=O.ssn and dateAdd(hour,16,O.testdate)<>createtime) and
not exists(select 1 from repair where ssn=O.ssn and station=O.station
and dateAdd(hour,16,O.testdate)=createtime)
/*
J0145A 2004-07-01 15:45:30.000 FA
J0145A 2004-07-02 04:15:46.000 FB
J0145B 2004-07-05 04:45:25.000 FA
J0145B 2004-07-06 13:52:12.000 FA
J0145C 2004-07-08 16:53:56.000 FB*/