MySQL中的排名功能

Question

MySQL中的排名功能

我需要找出客户的排名。在这里，我为我的要求添加了相应的ANSI标准SQL查询。请帮我转换为MySQL。

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
  FirstName, 
  Age,
  Gender 
FROM Person

有没有找到MySQL排名的函数？

Answer 1

一种选择是使用排名变量，例如：SELECT    first_name,          age,          gender,          @curRank := @curRank + 1 AS rankFROM      person p, (SELECT @curRank := 0) rORDER BY  age;该(SELECT @curRank := 0)部分允许变量初始化而无需单独的SET命令。测试用例：CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));INSERT INTO person VALUES (1, 'Bob', 25, 'M');INSERT INTO person VALUES (2, 'Jane', 20, 'F');INSERT INTO person VALUES (3, 'Jack', 30, 'M');INSERT INTO person VALUES (4, 'Bill', 32, 'M');INSERT INTO person VALUES (5, 'Nick', 22, 'M');INSERT INTO person VALUES (6, 'Kathy', 18, 'F');INSERT INTO person VALUES (7, 'Steve', 36, 'M');INSERT INTO person VALUES (8, 'Anne', 25, 'F');结果：+------------+------+--------+------+| first_name | age  | gender | rank |+------------+------+--------+------+| Kathy      |   18 | F      |    1 || Jane       |   20 | F      |    2 || Nick       |   22 | M      |    3 || Bob        |   25 | M      |    4 || Anne       |   25 | F      |    5 || Jack       |   30 | M      |    6 || Bill       |   32 | M      |    7 || Steve      |   36 | M      |    8 |+------------+------+--------+------+8 rows in set (0.02 sec)

Answer 2

这是一个通用的解决方案，它将分区上的密集排名分配给行。它使用用户变量：CREATE TABLE person (&nbsp; &nbsp; id INT NOT NULL PRIMARY KEY,&nbsp; &nbsp; firstname VARCHAR(10),&nbsp; &nbsp; gender VARCHAR(1),&nbsp; &nbsp; age INT);INSERT INTO person (id, firstname, gender, age) VALUES(1,&nbsp; 'Adams',&nbsp; 'M', 33),(2,&nbsp; 'Matt',&nbsp; &nbsp;'M', 31),(3,&nbsp; 'Grace',&nbsp; 'F', 25),(4,&nbsp; 'Harry',&nbsp; 'M', 20),(5,&nbsp; 'Scott',&nbsp; 'M', 30),(6,&nbsp; 'Sarah',&nbsp; 'F', 30),(7,&nbsp; 'Tony',&nbsp; &nbsp;'M', 30),(8,&nbsp; 'Lucy',&nbsp; &nbsp;'F', 27),(9,&nbsp; 'Zoe',&nbsp; &nbsp; 'F', 30),(10, 'Megan',&nbsp; 'F', 26),(11, 'Emily',&nbsp; 'F', 20),(12, 'Peter',&nbsp; 'M', 20),(13, 'John',&nbsp; &nbsp;'M', 21),(14, 'Kate',&nbsp; &nbsp;'F', 35),(15, 'James',&nbsp; 'M', 32),(16, 'Cole',&nbsp; &nbsp;'M', 25),(17, 'Dennis', 'M', 27),(18, 'Smith',&nbsp; 'M', 35),(19, 'Zack',&nbsp; &nbsp;'M', 35),(20, 'Jill',&nbsp; &nbsp;'F', 25);SELECT person.*, @rank := CASE&nbsp; &nbsp; WHEN @partval = gender AND @rankval = age THEN @rank&nbsp; &nbsp; WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1&nbsp; &nbsp; WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1END AS rnkFROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS xORDER BY gender, age;请注意，变量赋值放在CASE表达式中。这（理论上）负责评估问题的顺序。将IS NOT NULL被添加到处理数据类型转换和短路的问题。PS：通过删除检查绑定的所有条件，可以轻松地将其转换为分区上的行号。| id | firstname | gender | age | rank ||----|-----------|--------|-----|------|| 11 | Emily&nbsp; &nbsp; &nbsp;| F&nbsp; &nbsp; &nbsp; | 20&nbsp; | 1&nbsp; &nbsp; || 20 | Jill&nbsp; &nbsp; &nbsp; | F&nbsp; &nbsp; &nbsp; | 25&nbsp; | 2&nbsp; &nbsp; || 3&nbsp; | Grace&nbsp; &nbsp; &nbsp;| F&nbsp; &nbsp; &nbsp; | 25&nbsp; | 2&nbsp; &nbsp; || 10 | Megan&nbsp; &nbsp; &nbsp;| F&nbsp; &nbsp; &nbsp; | 26&nbsp; | 3&nbsp; &nbsp; || 8&nbsp; | Lucy&nbsp; &nbsp; &nbsp; | F&nbsp; &nbsp; &nbsp; | 27&nbsp; | 4&nbsp; &nbsp; || 6&nbsp; | Sarah&nbsp; &nbsp; &nbsp;| F&nbsp; &nbsp; &nbsp; | 30&nbsp; | 5&nbsp; &nbsp; || 9&nbsp; | Zoe&nbsp; &nbsp; &nbsp; &nbsp;| F&nbsp; &nbsp; &nbsp; | 30&nbsp; | 5&nbsp; &nbsp; || 14 | Kate&nbsp; &nbsp; &nbsp; | F&nbsp; &nbsp; &nbsp; | 35&nbsp; | 6&nbsp; &nbsp; || 4&nbsp; | Harry&nbsp; &nbsp; &nbsp;| M&nbsp; &nbsp; &nbsp; | 20&nbsp; | 1&nbsp; &nbsp; || 12 | Peter&nbsp; &nbsp; &nbsp;| M&nbsp; &nbsp; &nbsp; | 20&nbsp; | 1&nbsp; &nbsp; || 13 | John&nbsp; &nbsp; &nbsp; | M&nbsp; &nbsp; &nbsp; | 21&nbsp; | 2&nbsp; &nbsp; || 16 | Cole&nbsp; &nbsp; &nbsp; | M&nbsp; &nbsp; &nbsp; | 25&nbsp; | 3&nbsp; &nbsp; || 17 | Dennis&nbsp; &nbsp; | M&nbsp; &nbsp; &nbsp; | 27&nbsp; | 4&nbsp; &nbsp; || 7&nbsp; | Tony&nbsp; &nbsp; &nbsp; | M&nbsp; &nbsp; &nbsp; | 30&nbsp; | 5&nbsp; &nbsp; || 5&nbsp; | Scott&nbsp; &nbsp; &nbsp;| M&nbsp; &nbsp; &nbsp; | 30&nbsp; | 5&nbsp; &nbsp; || 2&nbsp; | Matt&nbsp; &nbsp; &nbsp; | M&nbsp; &nbsp; &nbsp; | 31&nbsp; | 6&nbsp; &nbsp; || 15 | James&nbsp; &nbsp; &nbsp;| M&nbsp; &nbsp; &nbsp; | 32&nbsp; | 7&nbsp; &nbsp; || 1&nbsp; | Adams&nbsp; &nbsp; &nbsp;| M&nbsp; &nbsp; &nbsp; | 33&nbsp; | 8&nbsp; &nbsp; || 18 | Smith&nbsp; &nbsp; &nbsp;| M&nbsp; &nbsp; &nbsp; | 35&nbsp; | 9&nbsp; &nbsp; || 19 | Zack&nbsp; &nbsp; &nbsp; | M&nbsp; &nbsp; &nbsp; | 35&nbsp; | 9&nbsp; &nbsp; |演示db <>小提琴

Answer 3

虽然最受欢迎的答案排名，但它没有分区，你可以自己加入以获得整个分区的东西：SELECT&nbsp; &nbsp; a.first_name,&nbsp; &nbsp; &nbsp; a.age,&nbsp; &nbsp; &nbsp; a.gender,&nbsp; &nbsp; &nbsp; &nbsp; count(b.age)+1 as rankFROM&nbsp; person a left join person b on a.age>b.age and a.gender=b.gender&nbsp;group by&nbsp; a.first_name,&nbsp; &nbsp; &nbsp; a.age,&nbsp; &nbsp; &nbsp; a.gender用例CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));INSERT INTO person VALUES (1, 'Bob', 25, 'M');INSERT INTO person VALUES (2, 'Jane', 20, 'F');INSERT INTO person VALUES (3, 'Jack', 30, 'M');INSERT INTO person VALUES (4, 'Bill', 32, 'M');INSERT INTO person VALUES (5, 'Nick', 22, 'M');INSERT INTO person VALUES (6, 'Kathy', 18, 'F');INSERT INTO person VALUES (7, 'Steve', 36, 'M');INSERT INTO person VALUES (8, 'Anne', 25, 'F');答案：Bill&nbsp; &nbsp; 32&nbsp; M&nbsp; &nbsp;4Bob&nbsp; &nbsp; &nbsp;25&nbsp; M&nbsp; &nbsp;2Jack&nbsp; &nbsp; 30&nbsp; M&nbsp; &nbsp;3Nick&nbsp; &nbsp; 22&nbsp; M&nbsp; &nbsp;1Steve&nbsp; &nbsp;36&nbsp; M&nbsp; &nbsp;5Anne&nbsp; &nbsp; 25&nbsp; F&nbsp; &nbsp;3Jane&nbsp; &nbsp; 20&nbsp; F&nbsp; &nbsp;2Kathy&nbsp; &nbsp;18&nbsp; F&nbsp; &nbsp;1