創(chuàng)建臨時表:臨時表在數(shù)據(jù)庫連接期間有效

  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

數(shù)據(jù)表.png

要求：
求每個類別下，成員數(shù)量最多的100個（每個類別的前一百名）

結果.png