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 rank

FROM      person p, (SELECT @curRank := 0) r

ORDER 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 (

    id INT NOT NULL PRIMARY KEY,

    firstname VARCHAR(10),

    gender VARCHAR(1),

    age INT

);

INSERT INTO person (id, firstname, gender, age) VALUES

(1,  'Adams',  'M', 33),

(2,  'Matt',   'M', 31),

(3,  'Grace',  'F', 25),

(4,  'Harry',  'M', 20),

(5,  'Scott',  'M', 30),

(6,  'Sarah',  'F', 30),

(7,  'Tony',   'M', 30),

(8,  'Lucy',   'F', 27),

(9,  'Zoe',    'F', 30),

(10, 'Megan',  'F', 26),

(11, 'Emily',  'F', 20),

(12, 'Peter',  'M', 20),

(13, 'John',   'M', 21),

(14, 'Kate',   'F', 35),

(15, 'James',  'M', 32),

(16, 'Cole',   'M', 25),

(17, 'Dennis', 'M', 27),

(18, 'Smith',  'M', 35),

(19, 'Zack',   'M', 35),

(20, 'Jill',   'F', 25);

SELECT person.*, @rank := CASE

    WHEN @partval = gender AND @rankval = age THEN @rank

    WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1

    WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1

END AS rnk

FROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x

ORDER BY gender, age;

请注意，变量赋值放在CASE表达式中。这（理论上）负责评估问题的顺序。将IS NOT NULL被添加到处理数据类型转换和短路的问题。

PS：通过删除检查绑定的所有条件，可以轻松地将其转换为分区上的行号。

| id | firstname | gender | age | rank |

|----|-----------|--------|-----|------|

| 11 | Emily     | F      | 20  | 1    |

| 20 | Jill      | F      | 25  | 2    |

| 3  | Grace     | F      | 25  | 2    |

| 10 | Megan     | F      | 26  | 3    |

| 8  | Lucy      | F      | 27  | 4    |

| 6  | Sarah     | F      | 30  | 5    |

| 9  | Zoe       | F      | 30  | 5    |

| 14 | Kate      | F      | 35  | 6    |

| 4  | Harry     | M      | 20  | 1    |

| 12 | Peter     | M      | 20  | 1    |

| 13 | John      | M      | 21  | 2    |

| 16 | Cole      | M      | 25  | 3    |

| 17 | Dennis    | M      | 27  | 4    |

| 7  | Tony      | M      | 30  | 5    |

| 5  | Scott     | M      | 30  | 5    |

| 2  | Matt      | M      | 31  | 6    |

| 15 | James     | M      | 32  | 7    |

| 1  | Adams     | M      | 33  | 8    |

| 18 | Smith     | M      | 35  | 9    |

| 19 | Zack      | M      | 35  | 9    |

演示db <>小提琴

Answer 3

虽然最受欢迎的答案排名，但它没有分区，你可以自己加入以获得整个分区的东西：

SELECT    a.first_name,

      a.age,

      a.gender,

        count(b.age)+1 as rank

FROM  person a left join person b on a.age>b.age and a.gender=b.gender 

group by  a.first_name,

      a.age,

      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    32  M   4

Bob     25  M   2

Jack    30  M   3

Nick    22  M   1

Steve   36  M   5

Anne    25  F   3

Jane    20  F   2

Kathy   18  F   1