SELECT 字段1,字段2,new_rank as rank from
(SELECT 字段1,字段2,
IF(@tmp=字段1,@rank:=@rank + 1,@rank:=1) as new_rank,
@tmp:=字段1 as tmp
FROM 表名
ORDER BY 字段1 DESC) b
where new_rank <= n;
逻辑说明:
根据分组字段排序
排序过程中将分组字段的值赋给变量@tmp(如果分组条件是多个字段,则@tmp也需要对应多个变量),然后通过if函数比对来排序
注意:@tmp:=字段1 as tmp 赋值操作需要放在右边,即if函数的右边,因为mysql语句编译有从右到左的特性
如果有join 每次请求 @rank 会有累加的问题 得像下面这样写
SELECT * FROM (
SELECT a.*,IF(@tmp=a.`creator_id`,@rank:=@rank + 1,@rank:=1) AS new_rank, @tmp:=a.`creator_id` AS tmp
FROM `commoditie` a
INNER JOIN `user_tag` b ON a.`creator_id`=b.`user_id` AND b.`type`=0
INNER JOIN ( SELECT @rank:=0,@tmp:='') d -- 如果表有 join 得加这一句,否则每次查询 @rank 会累加
WHERE a.`creator_id` NOT IN ('300cd73c-d270-11eb-a18a-6ba486d4a920' , '698725fa-d4a1-11eb-a65d-3d3a58964f15' ) ) c
WHERE new_rank <= 1 ORDER BY RAND() LIMIT 0,10 ;