mysql取分组后排序_MySQL实现分组排序（取第一或最后）

按order_num分组取时间最早的(LAST_UPDATE_DATE asc)，若要取最晚的改为desc

select ORDER_NUM,LAST_UPDATE_DATE,OPERATOR,rank from

(

select t.ORDER_NUM

,t.LAST_UPDATE_DATE

,t.OPERATOR

,@rownum:=@rownum+1 ,

if(@order_num=t.ORDER_NUM,@rank:=@rank+1,@rank:=1) as rank,

@order_num:=t.ORDER_NUM

from

(

select ORDER_NUM,LAST_UPDATE_DATE,OPERATOR from table_name

order by ORDER_NUM asc,LAST_UPDATE_DATE asc

) t

,(select @rownum :=0 , @order_num := null ,@rank:=0) a

) result

order by ORDER_NUM desc

最近经常处理mysql字符串，突然发现一种不一样的思路，也可以实现分组排序取第一或最后：

select ORDER_NUM

,case when locate('|',OPERATOR) > 0 then SUBSTR(OPERATOR,1,INSTR(OPERATOR,'|')-1) else OPERATOR end

from

(

SELECT ORDER_NUM

,group_concat(DISTINCT OPERATOR ORDER BY LAST_UPDATE_DATE SEPARATOR '|') AS OPERATOR

FROM table_name

GROUP BY ORDER_NUM

) result

update

最近看别人的面试题遇到一个需要按列的大小取最大的，在此记录下：

select id, greatest(score1,score2,score3) as score from score;

从score表中取出每个id最大的score(取最小则用least)

update at 2019/6/18

MySQL新版本已经支持窗口函数了，此文可以作废了：)