mysql實現row_number() 功能

標籤：custom   技術分享   _id   查詢   ase   where   number   RoCE   type   

查詢test表按group_id分組取sort_id前100個

SELECT id AS ‘原資料ID‘,group_id AS ‘分組ID‘,sort_id AS ‘排序條件‘,num AS ‘排名‘FROM     (SELECT  @row_number := CASE WHEN  @customer_no = group_id  THEN  @row_number + 1  ELSE  1 END   AS  num /*如果同group_id那麼排名加1,否則更新為1*/    ,@customer_no := group_id  AS  group_id /*重新載入group_id*/    ,id , sort_id /*原有資料*/ FROM  test,( SELECT  @customer_no := 0,@row_number := 0)  AS  t ORDER BY group_id ,sort_id DESC ,id)a WHERE num<=100

mysql實現row_number() 功能