mysql组内并列排名_MySQL 排名、分组后组内排名、取各组的前几名 及排名后更新插入数据表中...

一、排名

/*普通排名：从1开始，顺序往下排*/

SELECT cs.*,@r :=@r + 1 ASrankFROM cs,(SELECT @r := 0) rORDER BY score;

/*并列排名：相同的值是相同的排名*/

SELECT cs.*,CASE

WHEN @p=score THEN @r

WHEN @p:=score THEN @r:=@r+1

ENDrankFROM cs,(SELECT @r:=0,@p:=NULL)rORDER BY score;

/*并列排名：相同的值名次相同，与上例中的并列排名不同*/

SELECTcity,score,rankFROM(SELECT cs.*,@c:=IF(@p=score,@c,@r) ASrank,@p:=score,@r:=@r+1

FROM cs ,(SELECT @p:=NULL,@r:=1,@c:=0)rORDER BYscore

)c

二、分组后组内排名

/*分组普通排名：顺序排名*/

SELECTcity,score,rankFROM(SELECT cs.*,IF(@p=city,@r:=@r+1,@r:=1) ASrank,@p:=cityFROM cs,(SELECT @p:=NULL,@r:=0)rORDER BYcity,score

)s;

复制代码

/*分组后并列排名：组内相同数值排名相同*/

SELECTcity,score,rankFROM(SELECT *,IF(@p=city,CASE

WHEN @s=score THEN @r

WHEN @s:=score THEN @r:=@r+1

END,@r:=1 ) ASrank,@p:=city,@s:=scoreFROM cs,(SELECT @p:=NULL,@s:=NULL,@r:=0)rORDER BYcity,score

)s;

三、分组后取各组的前两名

/*取每组分数高的前两个，法一*/

SELECTcity,score,rankFROM(SELECT *,IF(@p=city,CASE

WHEN @s=score THEN @r

WHEN @s:=score THEN @r:=@r+1

END,@r:=1 ) ASrank,@p:=city,@s:=scoreFROM cs,(SELECT @p:=NULL,@s:=NULL,@r:=0)rORDER BY city,score DESC)sWHERE rank <3;

/*分组后取前两个，法二*/

SELECT * FROMcs cWHERE(SELECT count(*) FROMcsWHERE city=c.city AND score>c.score )<2

ORDER BY city,score DESC

如何用mysql 查询出数据出数据排名,然后加编号;

表字段说明:查询e_Task_Result表 ,score代表分数,task_result_id 代表id

SELECT(@rowNO := @rowNo+1) ASrowno,

task_result_id ,scoreFROM(

(SELECT task_result_id ,score FROM e_task_result ORDER BY score DESC) a,(SELECT @rowNo :=0) b )

如何将查询出来的排名更新到表中

表字段说明:查询e_Task_Result表 ,score代表分数,task_result_id 代表id,rank代表排名

UPDATEe_task_result dLEFT JOIN (SELECT(@rowNO := @rowNo+1) ASrowno,

task_result_idFROM (SELECTtask_result_idFROMe_task_result etrORDER BY task_result_status DESC,etr.score DESC,end_date) a,

(SELECT

@rowNO :=0) b) cON c.task_Result_id =d.task_result_idSET d.rank = rowno

再如：

UPDATE(SELECT @a := @a + 1 ASrank, user_code, task_typeFROM(select * fromuser_score_qualitywhere task_type =1

ORDER BY user_score_quality.last_finish_task_num desc, user_score_quality.update_time asc) h, (SELECT @a := 0) t

) t1, user_score_quality t2SET t2.last_rank =t1.rankWHERE t2.user_code = t1.user_code AND t2.task_type=t1.task_type

参考：

https://blog.csdn.net/w329636271/article/details/51751282

https://www.cnblogs.com/niniya/p/9046449.html

https://www.jianshu.com/p/bb1b72a1623e

http://blog.sina.com.cn/s/blog_4c197d420101e408.html

原文：https://www.cnblogs.com/yuluoxingkong/p/10606425.html