7.3.3. DISTINCT

7.3.3. DISTINCT

After the select list has been processed, the result table can optionally be subject to the elimination of duplicate rows. The DISTINCT key word is written directly after SELECT to specify this:

选择列表执行完后，也可以选择将结果集去重。SELECT后直接指定DISTINCT关键词可以实现该需求：

 

SELECT DISTINCT select_list ...

(Instead of DISTINCT the key word ALL can be used to specify the default behavior of retaining all rows.)

（可以使用关键字ALL来指定默认行为：保留所有行。）

 

Obviously, two rows are considered distinct if they differ in at least one column value. Null values are considered equal in this comparison.

很明显，如果两行至少有一个列值不同，则被认为是不同的。此比较中，空值视为相等。

 

Alternatively, an arbitrary expression can determine what rows are to be considered distinct:

可选的，可以使用一个表达式来决定distinct针对哪些行：

 

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

Here expression is an arbitrary value expression that is evaluated for all rows. A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the “first row” of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing occurs after ORDER BY sorting.)

这里的expression是一个针对所有行求值的任意值表达式。一组所有表达式均相等的行被视为重复行，并且仅该行集的第一行保留在输出中。请注意，除非查询在足够的列上排序以保证到达DISTINCT过滤器的行的唯一顺序，否则集合的“第一行”是不可预测的。（DISTINCT ON在ORDER BY排序之后进行处理。）

 

The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results. With judicious use of GROUP BY and subqueries in FROM, this construct can be avoided, but it is often the most convenient alternative.

DISTINCT ON子句并不是SQL标准的一部分，由于其结果可能不确定，因此有时被认为是不良语法。 通过明智地使用GROUP BY和FROM中的子查询，可以避免这种语法，但是它通常是最方便的选择。