************请教一SQL语句******************
表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
=====
但写法通不过.请高手指点


------解决方案--------------------
ls能查出lz想要的结果
但是lz说
---------------------
两表通过.
a.acarid=b.bcarid and a.aindendity=b.bindendity 关联
---------------------
跟结果联系不上
------解决方案--------------------
create table A(acarid int, aindendity varchar(10), adsetime datetime)
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