SQLSERVER GROUP BY查询最大一条，并关联当前行其它列

/*8月 A账户  余额  2017-08-078月 A账户  余额  2017-08-108月 A账户  余额  2017-08-128月 B账户  余额  2017-08-078月 B账户  余额  2017-08-208月 B账户  余额  2017-08-30结果  8月 A账户  余额  2017-08-128月 B账户  余额  2017-08-30*/;WITH dt_report AS(SELECT [month]='8月',[name]='A账户',[money]='150',[day]='2017-08-07'UNION ALLSELECT [month]='8月',[name]='A账户',[money]='100',[day]='2017-08-10'UNION ALLSELECT [month]='8月',[name]='A账户',[money]='50',[day]='2017-08-12'UNION ALLSELECT [month]='8月',[name]='B账户',[money]='50',[day]='2017-08-07'UNION ALLSELECT [month]='8月',[name]='B账户',[money]='250',[day]='2017-08-20'UNION ALLSELECT [month]='8月',[name]='B账户',[money]='20',[day]='2017-08-30')SELECT * INTO #dt_report FROM dt_reportSELECT * FROM #dt_report--方式一--SELECT #dt_report.month,#dt_report.name,MAX(#dt_report.[day])[day] INTO #dt_report_result FROM #dt_report GROUP BY #dt_report.month,#dt_report.name--SELECT a.*,b.money FROM #dt_report_result a--LEFT JOIN #dt_report b ON a.month=b.month AND a.name=b.name AND a.day=b.day--DROP TABLE #dt_report,#dt_report_result--方式二--SELECT a.*,b.money FROM (SELECT month,name,MAX(day)[day] FROM #dt_report GROUP BY month,name) a--LEFT JOIN #dt_report b ON a.month=b.month AND a.name=b.name AND a.day=b.day--方式三SELECT a.month,a.name,MAX(a.day)[day]  ,(SELECT TOP 1 b.money FROM #dt_report b WHERE b.month=a.month AND b.name=a.name AND b.day=MAX(a.day)) moneyFROM #dt_report a GROUP BY a.month,a.nameDROP TABLE #dt_report