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