一、方法一GROUP_CONCAT、SUBSTRING_INDEX
1、GROUP_CONCAT
2、SUBSTRING_INDEX
3、例子
首先我们可以首先根据job_id 排序然后根据start_time进行二级排序
select * from action_history
where left(start_time,10) = CURDATE()
order by job_id asc ,start_time desc
在下一步之前首先熟悉一下GROUP_CONCAT，这条语句会返回一个字符串，这个字符串由分组中的值连接组合而成。比如
select GROUP_CONCAT(status order by start_time desc )str from action_history;
然后在这条sql的基础上就可以使用
SUBSTRING_INDEX( GROUP_CONCAT(status order by start_time desc),',',1)
就能得到最新的状态了
完整语句为:
select
job_id,SUBSTRING_INDEX( GROUP_CONCAT(status order by start_time desc),',',1) status
from
(
select
job_id,status,start_time
from
action_history
where
left(start_time,10) = CURDATE()
order by job_id asc ,start_time desc
)b
GROUP BY job_id;
二、方法二
#1.将要进行分组的列进行order by(这个menu排序必须要这样做)
select userid,menu,score from test_domain.wxc order by menu asc ,score desc ;
#2.先判断pdept变量是否等于menu，如果相等rank变量相等，否则rank变量为1(所以在这之前必须要将menu进行排序)，然后把当前menu赋值给pdept变量，
select H.userid,H.menu,H.score,@rownum:=@rownum+1 rownum,
if(@pdept=H.menu,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=H.menu
from (
select userid,menu,score from test_domain.wxc order by menu asc ,score desc
) H ,(select @rownum :=0 , @pdept := null ,@rank:=0) a
;
#3.取出相应的字段即可
select userid,menu,score,rank,rownum from
(
select H.userid,H.menu,H.score,@rownum:=@rownum+1 rownum,
if(@pdept=H.menu,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=H.menu
from (
select userid,menu,score from test_domain.wxc order by menu asc ,score desc
) H ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result ;
整理自：
https://blog.csdn.net/su20145104009/article/details/78749854