SQL取某分组（或类别）中数值最大的一个

            WITH T1 AS
            (SELECT 
            EmployeeArchive.Person,
            RouteHIstory.Route RouteId,
            COUNT(OperatingData.OperatingDate) AS DayCount
            FROM BUS_TradingkmDoc TradingkmDoc
                LEFT JOIN BUS_OperatingData OperatingData ON TradingkmDoc.OperatingDataDoc = OperatingData.ID
                LEFT JOIN BUS_RouteHIstory RouteHIstory ON RouteHIstory.ID = OperatingData.RouteHistory
                LEFT JOIN CBO_EmployeeArchive EmployeeArchive ON EmployeeArchive.ID = TradingkmDoc.Pilot
            WHERE 
            OperatingData.OperatingDate BETWEEN '2021-03-01' AND '2021-03-31'           
            AND OperatingData.Org=1001309103393906 AND EmployeeArchive.Person IS NOT NULL
            GROUP BY Person,RouteHIstory.Route
            )
            SELECT T2.Person,T2.RouteId,R.Name AS RouteName,R.StarGrade,DBO.F_GetEnumName('UFIDA.U9.BUS.RouteBE.StarGradeEnum',R.StarGrade,'zh-cn') AS 线路星级 FROM (
               select *,ROW_NUMBER() OVER(PARTITION BY Person ORDER BY DayCount DESC) AS Num FROM T1
            )T2 LEFT JOIN BUS_Route R ON T2.RouteId=R.ID
            WHERE T2.Num=1 order by Person

 

主要代码：

select *,ROW_NUMBER() OVER(PARTITION BY Person ORDER BY DayCount DESC) AS Num FROM T1；  Person字段 DayCount最大的一个；