PostgreSQL 中的多个 DISTINCT ON 子句

是否可以选择以下行DISTINCT ON一些单独的、独立的列集？

假设我想要满足以下条件的所有行：

• 明显于(name, birth)
• 明显于(name, height)

因此，在下表中，标有红叉的行不会有所不同（并指示失败的子句）：

name      birth    height
--------------------------
William    1976      1.82
James      1981      1.68
Mike       1976      1.68
Tom        1967      1.79
William    1976      1.74   ❌ (name, birth)
William    1981      1.82   ❌ (name, height)
Tom        1978      1.92
Mike       1963      1.68   ❌ (name, height)
Tom        1971      1.86
James      1981      1.77   ❌ (name, birth)
Tom        1971      1.89   ❌ (name, birth)

在上面的例子中，如果DISTINCT ON条款刚刚被DISTINCT ON (name, birth, height)，那么所有行都将被视为不同的。

尝试过但没有成功：

• SELECT DISTINCT ON (name, birth) (name, height) ...
• SELECT DISTINCT ON (name, birth), (name, height) ...
• SELECT DISTINCT ON ((name, birth), (name, height)) ...
• SELECT DISTINCT ON (name, birth) AND (name, height) ...
• SELECT DISTINCT ON (name, birth) AND ON (name, height) ...
• SELECT DISTINCT ON (name, birth) DISTINCT ON (name, height) ...
• SELECT DISTINCT ON (name, birth), DISTINCT ON (name, height) ...

正如评论的 https://stackoverflow.com/questions/42505531/multiple-distinct-on-clauses-in-postgresql/42512809#comment72161230_42505531，问题中有歧义。每次调用的结果行数可能不同。如果您对任意结果感到满意，@klin的解决方案 https://stackoverflow.com/a/42505793/939860就足够好了。否则，您需要更严格地定义需求。喜欢：
明显于(name, birth)，首先选择最小的高度，然后选择最小的 ID 作为决胜局

Or:
明显于(name, height)，先选择最早出生的，然后选择最小的 ID 作为决胜局

您的表应该有一个主键（或some唯一标识行的方法）：

CREATE TEMP TABLE tbl (
  tbl_id serial PRIMARY KEY
, name text
, birth int
, height numeric);

INSERT INTO tbl (name, birth, height)
VALUES
  ('William', 1976, 1.82)
, ('James',   1981, 1.68)
, ('Mike',    1976, 1.68)
, ('Tom',     1967, 1.79)
, ('William', 1976, 1.74)
, ('William', 1981, 1.82)
, ('Tom',     1978, 1.92)
, ('Mike',    1963, 1.68)
, ('Tom',     1971, 1.86)
, ('James',   1981, 1.77)
, ('Tom',     1971, 1.89);

Query:

SELECT DISTINCT ON (name, height) *
FROM  (
   SELECT DISTINCT ON (name, birth) *
   FROM   tbl
   ORDER  BY name, birth, height, tbl_id  -- pick smallest height, ID as tiebreaker
   ) sub
ORDER  BY name, height, birth, tbl_id;    -- pick earliest birth, ID as tiebreaker

 tbl_id |  name   | birth | height
--------+---------+-------+--------
      2 | James   |  1981 |   1.68
      8 | Mike    |  1963 |   1.68
      4 | Tom     |  1967 |   1.79
      9 | Tom     |  1971 |   1.86
      7 | Tom     |  1978 |   1.92
      5 | William |  1976 |   1.74
      6 | William |  1981 |   1.82
(7 rows)    -- !!!

A DISTINCT ON没有确定性的查询ORDER BY可以从每组重复数据中返回任意行。应用一次，您仍然可以获得确定数量的行（任意选择）。重复应用，生成的行数也是任意的。有关的：

• 选择每个 GROUP BY 组中的第一行？ https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564