求助一个关于 group by 的问题

整体的思路是：

1. 把表分区，使得每个分区都有一个连续自增的排序字段

   对应的sql见下：

   SELECT 
   a.id, a.weight, a.category, COUNT(b.weight) + 1 AS rank
   FROM
   test_category a
       LEFT JOIN
   test_category b ON a.weight > b.weight
       AND a.category = b.category
   GROUP BY a.id , a.weight , a.category
   ORDER BY a.weight;

   查询结果如下：

   | id | weight | category | rank |
   |--- |------- |--------  |----- |
   | 4  | 40     | 2        | 1    |
   | 5  | 60     | 4        | 1    |
   | 6  | 90     | 4        | 2    |
   | 2  | 90     | 1        | 1    |
   | 1  | 100    | 1        | 2    |
   | 3  | 110    | 1        | 3    |

2. 通过限制每个分区的条数，获得目标结果

   sql见下

   SELECT 
   *
   FROM
   (SELECT 
       a.id, a.weight, a.category, COUNT(b.weight) + 1 AS rank
   FROM
       test_category a
   LEFT JOIN test_category b ON a.weight > b.weight
       AND a.category = b.category
   GROUP BY a.id , a.weight , a.category
   ORDER BY a.weight) AS b
   WHERE
   b.rank <= 2
   ORDER BY category ASC , weight ASC;

   b.rank<=2的意思是只去排名前2的数据，也就限制了每个分区大小为2.

组内排序的方法参考了 https://stackoverflow.com/questions/333366...