表a
acarid aindendity adsetime
1 1101 2007-01-10 10:00:00
1 1101 2007-01-10 13:00:00
1 1101 2007-01-10 15:00:00
表b
bcarid bindendity bdsetime
1 1101 2007-01-10 11:00:00
1 1101 2007-01-10 13:30:00
1 1101 2007-01-10 15:10:20
=====
想要的结果.
acarid aindendity adsetime bdsetime
1 1101 2007-01-10 10:00:00 2007-01-10 11:00:00
1 1101 2007-01-10 13:00:00 2007-01-10 13:30:00
1 1101 2007-01-10 15:00:00 2007-01-10 15:10:20
=======
两表通过.
a.acarid=b.bcarid and a.aindendity=b.bindendity 关联我写的SQL:
select a.*,b.bdsetime from a
left join (select top 1 * from b where a.acarid=b.bcarid and a.aindendity=b.bindendity and b.bdsetime>a.adsetime) as ls
a.acarid=ls.bcarid and a.aindendity=ls.bindendity
=====
但写法通不过.请高手指点
acarid aindendity adsetime
1 1101 2007-01-10 10:00:00
1 1101 2007-01-10 13:00:00
1 1101 2007-01-10 15:00:00
表b
bcarid bindendity bdsetime
1 1101 2007-01-10 11:00:00
1 1101 2007-01-10 13:30:00
1 1101 2007-01-10 15:10:20
=====
想要的结果.
acarid aindendity adsetime bdsetime
1 1101 2007-01-10 10:00:00 2007-01-10 11:00:00
1 1101 2007-01-10 13:00:00 2007-01-10 13:30:00
1 1101 2007-01-10 15:00:00 2007-01-10 15:10:20
=======
两表通过.
a.acarid=b.bcarid and a.aindendity=b.bindendity 关联我写的SQL:
select a.*,b.bdsetime from a
left join (select top 1 * from b where a.acarid=b.bcarid and a.aindendity=b.bindendity and b.bdsetime>a.adsetime) as ls
a.acarid=ls.bcarid and a.aindendity=ls.bindendity
=====
但写法通不过.请高手指点
select ID=identity(int, 1, 1), * into #B from Bselect * from #A, #B
where #A.acarid=#B.bcarid and #A.aindendity=#B.bindendity
insert A select 1, '1101', '2007-01-10 10:00:00'
union all select 1, '1101' , '2007-01-10 13:00:00'
union all select 1, '1101', '2007-01-10 15:00:00'
create table B(bcarid int, bindendity varchar(10), bdsetime datetime)
insert B select 1, '1101', '2007-01-10 11:00:00'
union all select 1, '1101', '2007-01-10 13:30:00'
union all select 1, '1101', '2007-01-10 15:10:20'select ID=identity(int, 1, 1), * into #A from A
select ID=identity(int, 1, 1), * into #B from Bselect #A. acarid, #A.aindendity, #A.adsetime, #B.bdsetime from #A, #B
where #A.ID=#B.ID--result
acarid aindendity adsetime bdsetime
----------- ---------- ------------------------------------------------------ ------------------------------------------------------
1 1101 2007-01-10 10:00:00.000 2007-01-10 11:00:00.000
1 1101 2007-01-10 13:00:00.000 2007-01-10 13:30:00.000
1 1101 2007-01-10 15:00:00.000 2007-01-10 15:10:20.000(3 row(s) affected)
但是lz说
---------------------
两表通过.
a.acarid=b.bcarid and a.aindendity=b.bindendity 关联
---------------------
跟结果联系不上
==
而且.两表的数量并不相等.
表a
acarid aindendity adsetime
1 1101 2007-01-10 10:00:00
1 1101 2007-01-10 13:00:00
1 1101 2007-01-10 15:00:00
表b
bcarid bindendity bdsetime
1 1101 2007-01-10 11:00:00
1 1101 2007-01-10 13:30:00
1 1101 2007-01-10 15:10:20
=====
想要的结果.
acarid aindendity adsetime bdsetime
1 1101 2007-01-10 10:00:00 2007-01-10 11:00:00
1 1101 2007-01-10 13:00:00 2007-01-10 13:30:00
2 1102 2007-01-10 15:00:00 2007-01-10 15:30:00
===========
这样.你的写法就错了.
刚才发错.
============表a
acarid aindendity adsetime
1 1101 2007-01-10 10:00:00
1 1101 2007-01-10 13:00:00
1 1101 2007-01-10 15:00:00
2 1102 2007-01-10 09:10:20
表b
bcarid bindendity bdsetime
1 1101 2007-01-10 11:00:00
1 1101 2007-01-10 13:30:00
2 1102 2007-01-10 10:10:20
=====
想要的结果.
acarid aindendity adsetime bdsetime
1 1101 2007-01-10 10:00:00 2007-01-10 11:00:00
1 1101 2007-01-10 13:00:00 2007-01-10 13:30:00
1 1101 2007-01-10 15:00:00 null
2 1102 2007-01-10 09:10:20 2007-01-10 10:10:20
===========
insert A select 1, '1101', '2007-01-10 10:00:00'
union all select 1, '1101', '2007-01-10 13:00:00'
union all select 1, '1101', '2007-01-10 15:00:00'
union all select 2, '1102', '2007-01-10 09:10:20'create table B(bcarid int, bindendity varchar(10), bdsetime datetime)
insert B select 1, '1101', '2007-01-10 11:00:00'
union all select 1, '1101', '2007-01-10 13:30:00'
union all select 2, '1102', '2007-01-10 10:10:20'
select *, bdsetime=(select min(bdsetime) from B where A.acarid=B.bcarid and A.aindendity=B.bindendity and A.adsetime<B.bdsetime) from A--result
acarid aindendity adsetime bdsetime
----------- ---------- ------------------------------------------------------ ------------------------------------------------------
1 1101 2007-01-10 10:00:00.000 2007-01-10 11:00:00.000
1 1101 2007-01-10 13:00:00.000 2007-01-10 13:30:00.000
1 1101 2007-01-10 15:00:00.000 NULL
2 1102 2007-01-10 09:10:20.000 2007-01-10 10:10:20.000(4 row(s) affected)
上面的SQL要符合三個條件
A.acarid=B.bcarid and A.aindendity=B.bindendity and A.adsetime<B.bdsetime