Cara menggunakan fungsi tetingkap MySQL untuk mencapai kedudukan senarai

Mula-mula, buat jadual ujian

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

dan kemudian biarkan chatGpt menjana beberapa data ujian untuk kami

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

Kemudian kita boleh mula menyedari keperluan kita: kembalikan senarai suka, Dan kembalikan ranking

rank()

Gunakan fungsi rank() untuk mengembalikan senarai suka, 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 |
+-------+------------+------+

bila Apabila menggunakan fungsi rank(), bilangan suka yang sama akan mendapat ranking yang sama, dan ranking mungkin melonjak, jadi ranking akhir tidak akan berterusan

pangkat_padat()

Gunakan fungsi dense_rank() untuk mengembalikan senarai like dense_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 |
+-------+------------+------+

Sama seperti fungsi rank(), bilangan suka yang sama akan mengembalikan ranking yang sama. Tetapi kedudukan akhir yang dikembalikan oleh dense_rank() ialah ranking berterusan

row_number()

Fungsi row_number() mengembalikan senarai suka, row_number() over()

Fungsi

 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() sesuai digunakan apabila senarai yang dikembalikan hanya memerlukan nombor siri

Tiga fungsi di atas baru ditambahkan pada MySQL 8.0, begitu juga dengan versi lama seperti MySQL. 5.7 kami Anda boleh mensimulasikannya dan mempelajari prinsip pelaksanaan ketiga-tiga fungsi tetingkap ini dengan cara

Pelaksanaan simulasi fungsi 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 |
+-------+------------+------+

Kita boleh menggunakan kaedah bergabung sendiri untuk membuat setiap skor lebih rendah daripada kiraan Rekod skor baris semasa, dan akhirnya menambah 1 pada nilai kiraan sebagai pemeringkatan baris semasa untuk mensimulasikan pelaksanaan simulasi 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 adalah serupa dengan pangkat Satu-satunya perbezaan ialah berbeza ditambah untuk menyahgandakan bilangan suka, supaya kedudukan yang dikembalikan untuk bilangan suka yang berbeza adalah berterusan

Pelaksanaan simulasi 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 |
+-------+------------+------------+

Kita boleh menggunakan pembolehubah A rowNum digunakan untuk merekodkan nombor baris Data rowNUm setiap baris ialah +1, supaya kita boleh mendapatkan nombor urutan yang kita mahu

.

Atas ialah kandungan terperinci Cara menggunakan fungsi tetingkap MySQL untuk mencapai kedudukan senarai. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!