postgreSQL 数据库基础 row_number 与distinct用法说明

PostgreSQL 数据库基础 row_number() 与 distinct 用法说明

在 PostgreSQL 数据库中，row_number() 和 distinct 是两个非常实用的函数，本文将详细介绍它们的用法。

row_number() 函数

row_number() 是一个窗口函数，用于为结果集中的每一行分配一个唯一的序号，它的基本语法如下：

SELECT column1, column2, ..., row_number() OVER (ORDER BY column_name) AS row_number
FROM table_name;

column1, column2, ... 是要查询的列名，table_name 是要查询的表名，column_name 是用于排序的列名。

row_number() 函数可以与 OVER() 子句一起使用，以指定结果集的排序方式，以下查询将按照 age 列降序排列，并为每一行分配一个序号：

SELECT name, age, row_number() OVER (ORDER BY age DESC) AS row_number
FROM students;

distinct 关键字

distinct 关键字用于去除查询结果中的重复行，它的基本语法如下：

SELECT DISTINCT column1, column2, ...
FROM table_name;

column1, column2, ... 是要查询的列名，table_name 是要查询的表名。

distinct 关键字可以用于任何列，也可以用于多个列，以下查询将返回所有不重复的学生姓名：

SELECT DISTINCT name
FROM students;

row_number() 与 distinct 结合使用

在某些情况下，我们可能需要同时使用 row_number() 和 distinct 函数，我们可能想要查询每个班级中成绩最高的学生姓名及其分数，这时，我们可以先使用 distinct 函数去除重复的班级信息，然后使用 row_number() 函数为每个班级的成绩进行排序，以下是具体的 SQL 语句：

WITH ranked_scores AS (
    SELECT class_id, name, score, DISTINCT ON (class_id) rank = row_number() OVER (PARTITION BY class_id ORDER BY score DESC)
    FROM students
)
SELECT class_id, name, score
FROM ranked_scores
WHERE rank = 1;

相关问题与解答

问题1：如何在 PostgreSQL 中使用 row_number() 函数？

答案：在 PostgreSQL 中，可以使用 row_number() 函数为结果集中的每一行分配一个唯一的序号，基本语法如下：SELECT column1, column2, ..., row_number() OVER (ORDER BY column_name) AS row_number FROM table_name;，column1, column2, ... 是要查询的列名，table_name 是要查询的表名，column_name 是用于排序的列名，row_number() 函数可以与 OVER() 子句一起使用，以指定结果集的排序方式。

问题2：如何在 PostgreSQL 中使用 distinct 关键字？

答案：在 PostgreSQL 中，可以使用 distinct 关键字去除查询结果中的重复行，基本语法如下：SELECT DISTINCT column1, column2, ... FROM table_name;，column1, column2, ... 是要查询的列名，table_name 是要查询的表名，distinct 关键字可以用于任何列，也可以用于多个列。

问题3：如何在 PostgreSQL 中同时使用 row_number() 和 distinct 函数？

答案：在 PostgreSQL 中，可以使用 CTE（公共表表达式）结合 row_number() 和 distinct 函数实现这一功能，首先使用 distinct 函数去除重复的行，然后使用 row_number() 函数为每个分组进行排序，从 CTE 中选择所需的列，示例 SQL 语句如下：WITH ranked_scores AS (SELECT class_id, name, score, DISTINCT ON (class_id) rank = row_number() OVER (PARTITION BY class_id ORDER BY score DESC) FROM students) SELECT class_id, name, score FROM ranked_scores WHERE rank = 1;。

问题4：在使用 row_number() 和 distinct 函数时，如何避免出现重复的序号？

答案：在使用 row_number() 和 distinct 函数时，可以通过添加额外的排序条件来避免出现重复的序号，如果需要对多个列进行排序，可以在 row_number() 函数中添加相应的排序条件，示例 SQL 语句如下：SELECT column1, column2, ..., row_number() OVER (PARTITION BY column3, column4 ORDER BY column5 DESC) AS row_number FROM table_name;，这样，即使有多个相同的值，也不会出现重复的序号。