mysql 加行号加排名多条件排名

最新推荐文章于 2022-10-24 17:20:04 发布

丫的都是昵称已存在

最新推荐文章于 2022-10-24 17:20:04 发布

阅读量191

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/fjgfkgjfg/article/details/117225940

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、加行号

SELECT
	@rownum := @rownum + 1 AS rownum,
	A.* 
FROM
	( SELECT * FROM tableA) A,(SELECT @rownum := 0 ) B;

二、加行号并加排名 @pre_score用来记录上一行的值

SELECT
    A.*
	@rownum := @rownum + 1 AS rownum,
    IF( @pre_score = A.score, @rank, @rank := @rownum ) AS rank,
	@pre_score:= A.score
FROM
	( SELECT score FROM tableA ) A,(SELECT @rank:=0,@rownum:=0,@pre_score:=null) B;

相同分数排名相同例：

rownum     score      rank
1          100        1
2	       99         2   
3          99         2
4          98         4
5          97         5

三、多条件排名

        SELECT
			A.*,
			IF(@pre_annual=A.annual and @pre_gangwei=A.gangwei,@rownum := @rownum + 1,@rownum:=1) rownum,	
		    IF( @pre_score = A.score, @rank, @rank := @rownum ) rank,
		    @pre_score := A.score,
			@pre_gangwei:=A.gangwei,
			@pre_annual:=annual
		FROM
			( SELECT id, yg_id, score, gangwei, annual FROM base_nt_dwdpm ORDER BY annual desc,gangwei ASC, score DESC ) A,(
			SELECT
				@rank := 0,
				@rownum := 0,
				@pre_score := NULL,
				@pre_gangwei := NULL,
				@pre_annual := NULL 
			) B

例子：

id    yg_id     score    gangwei   annual    rownum    rank
1	13000243	3.4000	    1	    2021	    1	    1
15	13100030	1.2000	    1	    2021	    2	    2
19	13100035	1.2000	    1	    2021	    3	    2
4	13100001	0.8000	    1	    2021	    4	    4
57	13100095	1.6000	    2	    2021	    1	    1
35	13100056	1.4000	    2	    2021	    2	    2
25	13100044	1.2000	    2	    2021	    3	    3
36	13100057	1.2000	    2	    2021	    4	    3
100	13000243	3.4000	    1	    2020	    1	    1
114	13100030	1.2000	    1	    2020	    2	    2
118	13100035	1.2000	    1	    2020	    3	    2
103	13100001	0.8000	    1	    2020	    4	    4