需求: 根据buyer_id分组,查出1000个订单
SELECT id,buyer_id,new_rank as rank , `status` from
(SELECT id, buyer_id,STATUS,
IF(@tmp=buyer_id,@rank:=@rank + 1,@rank:=1) as new_rank,
@tmp:=buyer_id as tmp
FROM `order`
where buyer_id in (16166,11,12,15,25,26,27,48,58,59,60,61,65,66,68,69,97,98,130,131,132,133,135,136,138,141,142,143,145,146)
ORDER BY buyer_id DESC) b
where new_rank<1001;
@rank := @rank + 1不能和group by 一起使用
百度可查询:在MySQL中实现Rank普通/高级并列排名函数 查询更多@rank用法