MySql 获取每个分组最新 10 条记录自定义变量


SELECT
    a.*
FROM
    (
    SELECT
        t1.*,
        @curr_cnt := t1.type AS curr_type,
        @rank := IF(
            @prev_cnt = @curr_cnt,
            @rank + 1,
            1
        ) AS rank,
        @prev_cnt := @curr_cnt AS prev_type
    FROM
        `goods` AS t1,
        (
        SELECT
            @curr_cnt := 0,
            @prev_cnt := 0,
            @rank := 0
    ) AS t2
ORDER BY
    t1.type,
    t1.addtime
DESC
) AS a
WHERE
    a.rank <= 10

查询逻辑

对 goods 表进行排序，先按分类 type 正序，再按上架时间 addtime 倒序


`goods` AS t1,

...中间省略

ORDER BY
    t1.type,
    t1.addtime

初始化变量，相当于 SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;


        SELECT
            @curr_cnt := 0,
            @prev_cnt := 0,
            @rank := 0

按分类进行编号，当 type 与上一条记录的 type 相同时编号 +1, 不同时编号重置为 1


        @curr_cnt := t1.type AS curr_type,
        @rank := IF(
            @prev_cnt = @curr_cnt,
            @rank + 1,
            1
        ) AS rank,
        @prev_cnt := @curr_cnt AS prev_type

筛选最新上架的 10 条记录 ( 编号 < 10 )


WHERE
    a.rank <= 10

查询逻辑#

查询逻辑