mysql_row_number()用法|极客笔记

mysql_row_number()用法

1. 介绍

在MySQL中，mysql_row_number()是一个用于计算行号的函数。它可以为查询结果中的每一行分配一个唯一的行号。这个函数在很多场景中都非常有用，比如需要对查询结果进行排序，并且需要为排序结果进行编号。

2. 语法

mysql_row_number()函数的语法如下：

mysql_row_number() OVER ([PARTITION BY col1[, col2, ...] ORDER BY col3[, col4, ...]])

PARTITION BY：可选的，根据指定的列对结果进行分区。相同的值将被分配相同的行号。如果省略PARTITION BY，则所有的行将被视为一组。
ORDER BY：必需的，指定用于排序的列。查询结果将根据这些列的值进行排序。如果ORDER BY中的列有重复的值，那么分配的行号可能会有所不同。

3. 示例

假设我们有一个名为students的表，其中包含以下几个字段：id，name，age，gender，score，分别表示学生的ID、姓名、年龄、性别和分数。现在我们希望按照学生的分数从高到低对他们进行排序，并为他们分配行号。

首先，我们需要创建一个示例表，并向其中插入一些数据：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(10),
    score INT
);

INSERT INTO students (name, age, gender, score) VALUES
    ('张三', 18, '男', 95),
    ('李四', 19, '女', 88),
    ('王五', 20, '男', 90),
    ('赵六', 18, '女', 92),
    ('刘七', 19, '男', 87),
    ('陈八', 20, '女', 95);

现在我们可以使用mysql_row_number()函数来计算行号，并按照分数降序排序：

SELECT 
    mysql_row_number() OVER (ORDER BY score DESC) AS row_number, 
    id, 
    name, 
    age, 
    gender, 
    score
FROM 
    students;

运行以上代码，我们将得到以下结果：

row_number	id	name	age	gender	score
1	1	张三	18	男	95
2	6	陈八	20	女	95
3	4	赵六	18	女	92
4	3	王五	20	男	90
5	2	李四	19	女	88
6	5	刘七	19	男	87

可以看到，在结果中每一行的前面都增加了一个新的列row_number，它表示了每个学生在排序结果中的行号。

4. 常见用法

4.1 对查询结果进行分组

mysql_row_number()函数可以通过指定PARTITION BY子句来对查询结果进行分组。例如，我们希望按照性别对学生进行分组，并计算每个分组内的行号：

SELECT 
    mysql_row_number() OVER (PARTITION BY gender ORDER BY score DESC) AS row_number, 
    id, 
    name, 
    age, 
    gender, 
    score
FROM 
    students;

运行以上代码，我们将得到以下结果：

row_number	id	name	age	gender	score
1	1	张三	18	男	95
2	3	王五	20	男	90
3	5	刘七	19	男	87
1	6	陈八	20	女	95
2	4	赵六	18	女	92
3	2	李四	19	女	88

我们可以看到，每个性别内的学生都有自己的行号，行号是根据分组后的排序结果进行分配的。

4.2 对查询结果进行筛选

mysql_row_number()函数还可以用于对查询结果进行筛选。我们可以使用WHERE子句来指定筛选条件，然后在外部查询中使用mysql_row_number()函数来计算行号。例如，我们希望只选择分数排名前三的学生：

SELECT *
FROM (
    SELECT 
        mysql_row_number() OVER (ORDER BY score DESC) AS row_number, 
        id, 
        name, 
        age, 
        gender, 
        score
    FROM 
        students
) AS subquery
WHERE row_number <= 3;

运行以上代码，我们将得到以下结果：