MySQL 창 기능을 사용하여 목록 순위를 얻는 방법-MySQL 튜토리얼-php.cn

MySQL 창 기능을 사용하여 목록 순위를 얻는 방법

王林

풀어 주다： 2023-05-26 10:09:28

앞으로

1001명이 탐색했습니다.

먼저 테스트 테이블을 만듭니다

create table praise_record(
    id bigint primary key auto_increment,
    name varchar(10),
    praise_num int
) ENGINE=InnoDB;

로그인 후 복사

그런 다음 chatGpt가 테스트 데이터를 생성하도록 합니다

INSERT INTO praise_record (name, praise_num) VALUES (&#39;John&#39;, 5);
INSERT INTO praise_record (name, praise_num) VALUES (&#39;Jane&#39;, 3);
INSERT INTO praise_record (name, praise_num) VALUES (&#39;Bob&#39;, 10);
INSERT INTO praise_record (name, praise_num) VALUES (&#39;Alice&#39;, 3);
INSERT INTO praise_record (name, praise_num) VALUES (&#39;David&#39;, 7);
INSERT INTO praise_record (name, praise_num) VALUES (&#39;oct&#39;, 7);

로그인 후 복사

그런 다음 우리의 요구 사항을 실현할 수 있습니다. 좋아요 목록을 반환하고 순위를 반환합니다.

rank()

사용 좋아요 목록을 반환하는 Rank() 함수, Rank() over()

## 注意这里返回的rank字段要用反引号包起来
select name, praise_num, rank() over (order by praise_num desc) as `rank` from praise_record;
+-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
+-------+------------+------+

로그인 후 복사

rank() 함수를 사용하면 같은 개수의 좋아요가 같은 순위를 얻게 되어 순위가 급등할 수 있습니다. 연속되지 않습니다

dense_rank()

dense_rank() 함수를 사용하여 좋아요 목록을 반환하고,density_rank()over()

 select name, praise_num, dense_rank() over (order by praise_num desc) as `rank` from praise_record;
 
 +-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    3 |
| Jane  |          3 |    4 |
| Alice |          3 |    4 |
+-------+------------+------+

로그인 후 복사

rank() 함수와 동일, 동일한 포인트 좋아요 수 동일한 순위를 반환하지만,density_rank()가 반환한 최종 순위는 연속 순위입니다.

row_number()

row_number() 함수는 좋아요 목록을 반환하고, row_number() over()

 select name, praise_num, row_number() over (order by praise_num desc) as `rank` from praise_record;
 +-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    3 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    6 |
+-------+------------+------+

로그인 후 복사

row_number ( ) 함수는 반환된 목록에 일련번호만 필요한 경우에 사용하기에 적합하다. 위 세 가지 함수는 모두 MySQL 8.0에 새로 추가된 것이므로 MySQL 5.7 등 이전 버전에서도 시뮬레이션하고 구현할 수 있으며, 이 세 가지 창에 대해 way.함수 구현 원리

rank() 함수의 시뮬레이션 구현

select p1.name, p1.praise_num, count(p2.praise_num) + 1 as `rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `rank`;
+-------+------------+------+
| name  | praise_num | rank |
+-------+------------+------+
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
+-------+------------+------+

로그인 후 복사

self-join 방법을 사용하여 현재 행 점수보다 낮은 점수를 가진 각 레코드를 계산하고 최종적으로 1을 더할 수 있습니다. 시뮬레이션할 현재 행의 순위로 값을 계산합니다. Rank()

dense_rank()

select p1.name, p1.praise_num, count(distinct p2.praise_num) + 1 as `dense_rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `dense_rank`;
+-------+------------+------------+
| name  | praise_num | dense_rank |
+-------+------------+------------+
| Bob   |         10 |          1 |
| oct   |          7 |          2 |
| David |          7 |          2 |
| John  |          5 |          3 |
| Jane  |          3 |          4 |
| Alice |          3 |          4 |
+-------+------------+------------+

로그인 후 복사

dense_rank의 시뮬레이션 구현은 순위와 유사합니다. 유일한 차이점은 좋아요 수를 중복 제거하기 위해 이 방식으로 추가된다는 점입니다. 좋아요 수에 따라 반환되는 순위는 연속

row_number

##使用自定义变量得先初始化
set @rowNum = 0;
select name, praise_num, @rowNum := @rowNum +1 as `row_number`  from praise_record order by praise_num desc ;
+-------+------------+------------+
| name  | praise_num | row_number |
+-------+------------+------------+
| Bob   |         10 |          1 |
| David |          7 |          2 |
| oct   |          7 |          3 |
| John  |          5 |          4 |
| Jane  |          3 |          5 |
| Alice |          3 |          6 |
+-------+------------+------------+

로그인 후 복사