MySQL 分组后在分组内排序，每个分组中取前 N 条

Question

获取各类别下点击数最多的前 2 个商品，并按点击数降序排序：

SELECT
  p_type,
  p_name,
  p_view,
  row_num
FROM
  (SELECT
    p_type,
    p_name,
    p_view,
    IF(@bak=p_type,@row_num:=@row_num+1,@row_num:=1) AS row_num,
    @bak:=p_type
  FROM
    (SELECT
      p_type,
      p_name,
      p_view
    FROM
      products
    GROUP BY
      p_type, p_name, p_view
    ORDER BY
      p_type, p_view DESC
    ) a, (SELECT @row_num:=0, @bak:='') b) c
WHERE
  row_num<=2

注意：

• 赋值与比较
  • 比较： @bak=p_type
  • 赋值： @bak:=p_type
• 代码顺序
  • 调换 IF(@bak=p_type,@row_num:=@row_num+1,@row_num:=1) AS row_num 与 @bak:=p_type 顺序将导致结果出错