创建临时表:临时表在数据库连接期间有效
set @num:=0,@ptype:='',@stype='';
DROP TABLE
IF EXISTS `tmp_result`;
create TEMPORARY table tmp_result
select n.*,
@num := if(@ptype=product_name and @stype=store_number ,@num+1,1) as rank,
@ptype := n.product_name as pname,
@stype := n.store_number as snumber
from noodle as n
where receipt_number>0
order by product_name,store_number,order_time asc
select *
from tmp_result
数据表.png
要求：
求每个类别下，成员数量最多的100个（每个类别的前一百名）
结果.png
网友评论