【数据库】PostgreSQL中的DISTINCT ON和DISTINCT的区别

深入理解PostgreSQL中的DISTINCT ON和DISTINCT

在数据库查询中，我们经常会遇到需要去除重复数据的情况。在PostgreSQL中，我们可以使用DISTINCT和DISTINCT ON来实现这个目标。那么，它们之间有什么区别呢？本文将详细介绍这两种方法的用法、区别以及适用场景。

DISTINCT是SQL中的一个关键字，用于从查询结果中去除重复的行。它的基本语法如下：

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

这里的column1, column2, …表示需要去重的列名，table_name表示表名。例如，我们有一个名为students的表，包含id、name和age三个字段，我们想要查询所有不重复的名字，可以使用以下语句：

SELECT DISTINCT name
FROM students;

执行上述语句后，我们会得到一个包含所有不重复名字的结果集。

虽然DISTINCT可以去除重复的行，但它有一个明显的局限性：它只能作用于所有选定的列。也就是说，如果多个列的值相同，但其他列的值不同，那么这些行仍然会被当作重复行处理。为了解决这个问题，PostgreSQL提供了DISTINCT ON关键字。

DISTINCT ON与DISTINCT类似，也是用于去除重复行。但它允许我们对多个列进行去重操作，而且可以根据指定的列对结果集进行排序。DISTINCT ON的基本语法如下：

SELECT DISTINCT ON (column1, column2, ...) column1, column2, ...
FROM table_name
ORDER BY column1, column2, ...
;

这里的column1, column2, …表示需要去重的列名，table_name表示表名。例如，我们有一个名为students的表，包含id、name和age三个字段，我们想要查询每个班级中年龄最大的学生信息，可以使用以下语句：

SELECT id, name, age, class_id
FROM students
ORDER BY class_id, age DESC
DISTINCT ON (class_id, age);

执行上述语句后，我们会得到一个包含每个班级中年龄最大的学生信息的按班级和年龄降序排列的结果集。注意，如果有多个学生的年龄相同且最大，那么只会返回其中一个学生的信息。

通过上面的介绍，我们可以看到DISTINCT和DISTINCT ON的主要区别在于：

DISTINCT作用于所有选定的列，而DISTINCT ON允许我们对多个列进行去重操作。
DISTINCT会去除所有重复的行，而DISTINCT ON只会去除部分重复的行。具体来说，它会保留每个分组中的第一个出现的行（根据ORDER BY子句排序）。如果有多个行具有相同的值，那么只有第一个出现的行会被保留。
DISTINCT不会改变查询结果的顺序，而DISTINCT ON会根据指定的列对结果集进行排序。
DISTINCT不能与其他聚合函数一起使用，而DISTINCT ON可以与GROUP BY子句一起使用。例如，我们可以使用以下语句查询每个班级的平均年龄：

SELECT class_id, AVG(age) as average_age
FROM students
GROUP BY class_id
ORDER BY class_id, average_age DESC;

总结一下，DISTINCT和DISTINCT ON都是PostgreSQL中用于去除重复行的方法，但它们的用法和限制有所不同。在实际开发中，我们需要根据具体需求选择合适的方法来优化查询性能。希望本文能帮助你更好地理解和使用这两种方法。