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
