A表
id title
a1 a1
b表
id Aid name time
b1 a1 u 2017-5-6
b2 a1 x 2017-7-8
b3 a1 z 2017-2-9
显示成
b2 a1 x 2017-07-08
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] nvarchar(22),[title] nvarchar(22))
Insert #A
select N'a1',N'a1'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([id] nvarchar(22),[Aid] nvarchar(22),[name] nvarchar(21),[time] DateTIME)
Insert #B
select N'b1',N'a1',N'u','2017-5-6' union all
select N'b2',N'a1',N'x','2017-7-8' union all
select N'b3',N'a1',N'z','2017-2-9'
Go
select * from #A
select * from #B
;WITH cte AS (
SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY Aid ORDER BY time DESC ) AS num
FROM #B
)
SELECT cte.id ,
cte.Aid ,
cte.name ,
cte.time
FROM cte
JOIN #A ON #A.id = cte.Aid
WHERE cte.num = 1;