TOP N rows for a grouped query
方法一 SELECT ac.* FROM mytable AC LEFT JOIN mytable A ON A.id = AC.id WHERE A.id IN ( SELECT TOP 3 id FROM mytable A WHERE A.category = AC.category ORDER BY A.createtime DESC ) ORDER BY AC.category 方法二(使用Having) SELECT c.*, d.ranknum FROM mytable AS c INNER JOIN ( SELECT a.id, COUNT(*) AS ranknum FROM mytable AS a INNER JOIN mytable AS b ON (a.category = b.category) AND (a.num <= b.num) GROUP BY a.id HAVING COUNT(*) <= 3 ) AS d ON (c.id = d.id) ORDER BY c.category, d.ranknum SELECT AC.* FROM historypath AC INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY A.car ORDER BY A.id) AS RowNumber, A.car FROM historypath A ) A ON A.car = AC.car WHERE A.RowNumber <= 3 SELECT& AC.* FROM historypath AC CROSS APPLY (SELECT TOP 3 id FROM historypath A WHERE A.car = AC.car ORDER BY A.id desc ) A ORDER BY A.id desc
