select a.id,a.name,b.time as time1,c.time as time2 from 表a a inner join (select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time<b.time)<1) b on a.id=b.id inner join (select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time>b.time)<1) c on a.id=b.id
写错了几个地方!select a.id,a.name,b.time as time1,c.time as time2 from 表a a inner join (select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time<b.time)<1) b on a.id=b.a_id inner join (select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time>b.time)<1) c on a.id=c.a_id
select A.a_id,A.times1,B.times2 from (select * from (select A.a_id ,(case when A.times =(select max(B.times) from b B where B.a_id=A.a_id group by B.a_id ) then A.times else null end) AS times1 from (SELECT * FROM dbo.b A WHERE (id IN (SELECT TOP 2 id FROM b B WHERE A.a_id = B.a_id ORDER BY times DESC))) A) A where not times1 is null) A LEFT OUTER JOIN (select * from (select A.a_id ,(case when A.times <>(select max(B.times) from b B where B.a_id=A.a_id group by B.a_id ) then A.times else null end) AS times2 from (SELECT * FROM dbo.b A WHERE (id IN (SELECT TOP 2 id FROM b B WHERE A.a_id = B.a_id ORDER BY times DESC))) A) A where not times2 is null) B on A.a_id=B.a_id
自己搞定了,谢谢大家的提醒!select aaa.id,aaa.name,bbb.time as time1,ccc.time as time2 from 表a aaa left join (select * from 表b aa where aa.time in (select top 1 bb.time from 表b bb where aa.id=bb.id order by time desc) ) bbb on aaa.id=bbb.a_id left join (select * from 表b aa where aa.time in (select top 1 bb.time from 表b bb where aa.id=bb.id and bb.time not in (select top 1 cc.time from 表b cc where bb.id=cc.id order by time desc) order by time desc) ) ccc on aaa.id=ccc.a_id
(select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time<b.time)<1) b on a.id=b.id
inner join
(select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time>b.time)<1) c on a.id=b.id
(select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time<b.time)<1) b on a.id=b.a_id
inner join
(select * from 表b a where (select count(1) from 表b b where a.a_id=b.a_id and a.time>b.time)<1) c on a.id=c.a_id
(select * from (select A.a_id ,(case when A.times =(select max(B.times) from b B where B.a_id=A.a_id group by B.a_id ) then A.times else null end) AS times1
from (SELECT * FROM dbo.b A WHERE (id IN (SELECT TOP 2 id FROM b B WHERE A.a_id = B.a_id ORDER BY times DESC))) A) A where not times1 is null) A
LEFT OUTER JOIN
(select * from (select A.a_id ,(case when A.times <>(select max(B.times) from b B where B.a_id=A.a_id group by B.a_id ) then A.times else null end) AS times2
from (SELECT * FROM dbo.b A WHERE (id IN (SELECT TOP 2 id FROM b B WHERE A.a_id = B.a_id ORDER BY times DESC))) A) A where not times2 is null) B
on A.a_id=B.a_id
from 表a aaa
left join
(select * from 表b aa
where aa.time in (select top 1 bb.time from 表b bb
where aa.id=bb.id order by time desc)
) bbb
on aaa.id=bbb.a_id
left join
(select * from 表b aa
where aa.time in (select top 1 bb.time from 表b bb
where aa.id=bb.id
and bb.time not in
(select top 1 cc.time from 表b cc
where bb.id=cc.id order by time desc)
order by time desc)
) ccc
on aaa.id=ccc.a_id