MSSQL 求用户最近一次消费记录的三种写法

IF OBJECT_ID('TempDB.dbo.#tbl_User') IS NOT NULL
	Drop Table #tbl_User
GO
CREATE TABLE #tbl_User
(
	uid		int,
	name	Nvarchar(64)
)
GO
IF OBJECT_ID('TempDB.dbo.#tbl_Records') IS NOT NULL
	DROP TABLE #tbl_Records
GO
CREATE TABLE #tbl_Records
(
	id					int  identity(1,1),
	uid					int,
	consumptiontime		datetime
)
INSERT INTO #tbl_User(uid,name)values(1,N'Tom'),(2,N'Jerry')
INSERT INTO #tbl_Records(uid,consumptiontime)values(1,'2019-01-01'),(1,'2019-02-01'),(1,'2019-03-01'),(2,'2019-05-01'),(2,'2019-06-01')


--way 1
SELECT u.name,b.consumptiontime
FROM #tbl_User u
left join (select r.uid,
				  max(r.consumptiontime) as consumptiontime 
			From #tbl_Records r 
			group by uid
		  ) b
on u.uid=b.uid

--way 2
select u.name,r.consumptiontime
From #tbl_Records r 
inner join #tbl_User u on r.uid=u.uid
where not exists(select 1 From #tbl_Records where #tbl_Records.uid=r.uid and #tbl_Records.consumptiontime>r.consumptiontime)

--way 3
select u.name,(select MAX(consumptiontime) as consumptiontime from #tbl_Records r where r.uid=u.uid) as consumptiontime
From #tbl_User u