[大数据]mysql 排名

1、不分组排名

1、连续排名

## mysql 8
SELECT
	score,ranking 
FROM
	(
	SELECT
		s.score,
		ROW_NUMBER () OVER ( ORDER BY score DESC ) ranking 
	FROM
		(
		SELECT 0 AS score 
		UNION ALL
		SELECT 0 AS score
	    UNION ALL
		SELECT 0 AS score 
		UNION ALL
		SELECT 1 AS score 
		UNION ALL
		SELECT 2 AS score 
		UNION ALL
		SELECT 1 AS score 
		) s
		ORDER BY
		s.score DESC 
	) a;

## mysql5.7
select score,ranking
from (
	SELECT 
	 s.score,
	(@cur_rank := @cur_rank + 1) AS ranking
	 
	FROM (
		select 0 as score 
		union all
		select 0 as score 
		union all
		select 0 as score 
		union all
		select 1 as score
		union all
		select 2 as score
		union all 
		select 1 as score 
	)s,(SELECT @cur_rank := 0)  r 
	ORDER BY s.score desc
) a 
;

2、跳跃并列排名

## mysql8
SELECT
	score,
	ranking 
FROM
	(
	SELECT
		s.score,
		RANK() OVER(ORDER BY score DESC)as ranking
	FROM
		(
		SELECT
			0 AS score UNION ALL
		SELECT
			0 AS score UNION ALL
		SELECT
			0 AS score UNION ALL
		SELECT
			1 AS score UNION ALL
		SELECT
			2 AS score UNION ALL
		SELECT
			1 AS score 
			) s
	) a;

##mysql 5.7
select score,ranking
from (
	SELECT 
	 s.score,
	@cur_count := @cur_count + 1,
 if(@pre_score = s.score,@cur_rank,@cur_rank := @cur_count) ranking,
 @pre_score := s.score
	FROM (

		select 0 as score 
		union all
		select 0 as score 
		union all
		select 0 as score 
		union all
		select 1 as score
		union all
		select 2 as score
		union all 
		select 1 as score 

	)s,(SELECT @cur_count := 0,@cur_rank:=0,@pre_score := NULL)  r 
	ORDER BY s.score desc
) a 
;

3、连续并列排名

##mysql 8
SELECT
	score,
	ranking 
FROM
	(
	SELECT
		s.score,
DENSE_RANK() OVER(ORDER BY score DESC)as ranking
	FROM
		(
		SELECT
			0 AS score UNION ALL
		SELECT
			0 AS score UNION ALL
		SELECT
			0 AS score UNION ALL
		SELECT
			1 AS score UNION ALL
		SELECT
			2 AS score UNION ALL
		SELECT
			1 AS score 
			) s
	) a;

##mysql5.7
## if  实现连续并列排名
select score,ranking
from (
	SELECT 
	 s.score,
	 IF(@pre_score = score, @cur_rank , @cur_rank :=@cur_rank +1 ) AS ranking,
	@pre_score := score

	FROM (
		select 0 as score 
		union all
		select 0 as score 
		union all
		select 0 as score 
		union all
		select 1 as score
		union all
		select 2 as score
	)s,(SELECT @pre_score := null ,@cur_rank := 0)  r 
	ORDER BY s.score desc
) a 
;

## 连续并列排名  case ...when ... then  遇到零会出现空排名
select score,ranking
from (
	SELECT 
	 s.score,
	 CASE
		WHEN @pre_score = score THEN  @cur_rank
		WHEN @pre_score := score THEN @cur_rank :=@cur_rank +1
	END AS ranking
	 
	FROM (
		select 0 as score 
		union all
		select 0 as score 
		union all
		select 0 as score 
		union all
		select 1 as score
		union all
		select 2 as score
	)s,(SELECT @pre_score := null ,@cur_rank := 0)  r 
	ORDER BY s.score desc
) a 
;

2、分组排名

1、分组连续排名

##mysql 8
select 
cid,score,ranking
from (
	SELECT 
	 s.score,
	 s.cid,
	ROW_NUMBER() OVER (PARTITION BY s.cid ORDER BY s.score DESC) ranking
	 
	FROM (
		select 0 as score , '第一组' as cid
		union all
		select 0 as score , '第一组' as cid
		union all
		select 0 as score  , '第二组' as cid
		union all
		select 1 as score , '第二组' as cid
		union all
		select 2 as score , '第一组' as cid
	)s
) a 
;


##mysql5.7
select 
cid,score,ranking
from (
	SELECT 
	 	s.score,
		s.cid,
	 	IF(@pre_cid = cid, @cur_rank :=@cur_rank +1,@cur_rank :=1) AS ranking,
		@pre_cid := cid
	 
	FROM (
		select 0 as score , '第一组' as cid
		union all
		select 0 as score , '第一组' as cid
		union all
		select 0 as score  , '第二组' as cid
		union all
		select 1 as score , '第二组' as cid
		union all
		select 2 as score , '第一组' as cid
	)s,(SELECT @pre_cid := null ,@cur_rank := 0)  r 
	ORDER BY s.cid,s.score desc
) a 
;

2、分组并列跳跃排名

##mysql 8
select 
cid,score,ranking
from (
	SELECT 
	 	s.score,
		s.cid,
		RANK() OVER(PARTITION BY s.cid ORDER BY s.score DESC) ranking
	 
	FROM (
		select 0 as score , '第一组' as cid
		union all
		select 0 as score , '第一组' as cid
		union all
		select 0 as score  , '第二组' as cid
		union all
		select 1 as score , '第二组' as cid
		union all
		select 2 as score , '第一组' as cid
	)s
) a 
;


##mysql5.7

select 
cid,score,ranking
from (
SELECT s.cid, s.score,
IF(@pre_cid = s.cid,
   @rank_counter := @rank_counter + 1,
   @rank_counter := 1) temp1,
IF(@pre_cid = s.cid,
   IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter),
   @cur_rank := 1) ranking,
@pre_score := s.score temp2,
@pre_cid := s.cid temp3
FROM (	select 0 as score , '第一组' as cid
		union all
		select 0 as score , '第一组' as cid
		union all
		select 0 as score  , '第二组' as cid
		union all
		select 1 as score , '第二组' as cid
		union all
		select 2 as score , '第一组' as cid) s, (SELECT @cur_rank := 0, @pre_cid := NULL, @pre_score := NULL, @rank_counter := 1)r
ORDER BY s.cid, s.score DESC
) a 
;

3、分组并列连续排名

##mysql8
select 
cid,score,ranking
from (
SELECT s.cid, s.score,
DENSE_RANK() OVER(PARTITION BY s.cid ORDER BY s.score DESC)ranking
FROM (	select 0 as score , '第一组' as cid
		union all
		select 0 as score , '第一组' as cid
		union all
		select 0 as score  , '第二组' as cid
		union all
		select 1 as score , '第二组' as cid
		union all
		select 2 as score , '第一组' as cid) s
) a 
;



##mysql.7
select 
cid,score,ranking
from (
SELECT s.cid, s.score,
IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) temp1,
@pre_score := s.score temp2,
IF(@pre_cid = s.cid, @cur_rank, @cur_rank := 1) ranking,
@pre_cid := s.cid temp3
FROM (	select 0 as score , '第一组' as cid
		union all
		select 0 as score , '第一组' as cid
		union all
		select 0 as score  , '第二组' as cid
		union all
		select 1 as score , '第二组' as cid
		union all
		select 2 as score , '第一组' as cid) s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_cid := NULL) r
ORDER BY cid, score DESC
) a 
;

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖