mysql 同表提取前N记录方法

主要应用于按字段分类提取前N的需求

 

 
SELECT
  CASE 
    WHEN @cn != classid THEN @rownum:= 1 
    ELSE @rownum:= @rownum + 1  
    END AS No,
  @cn := classid AS classid1,
  classid,
  less_date,
  fieldnum
FROM
  (SELECT @rownum:=0) r,
  (SELECT @cn:='') p,
  tab_less
where del_flag='0' and less_date>=now()
ORDER BY
   classid,less_date  
 

转载于:https://my.oschina.net/weakupman/blog/758140