SqlServer 分组取前三（开窗函数）

--创建临时表
SELECT * INTO #TEMP FROM 
(
    SELECT 'AA' NAME,100 AS NUM,'1' AS DEPT UNION
    SELECT 'BB',200,'1' UNION
    SELECT 'CC',300,'1' UNION
    SELECT 'DD',400,'1' UNION
    SELECT 'EE',800,'2' UNION
    SELECT 'FF',700,'2' UNION
    SELECT 'GG',600,'2' UNION
    SELECT 'HH',500,'2'
) A

--利用ROW_NUMBER() 取前N
SELECT * FROM
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY NUM DESC) AS ROWNUM FROM #TEMP
) AS RANKED WHERE RANKED.ROWNUM <= 3 ORDER BY DEPT,NUM,NAME

DROP TABLE #TEMP