sql - 加入一对多并检索单个结果

Question

我有两个表，如果重要的话，在 PostgreSQL 中，具有一对多关系。我需要加入他们，以便对于每个“一个”，我只能从“多个”表中获得一个结果。不仅如此，我还需要从“许多”表中挑出特定结果。

TABLE_AID  | NAME      | DATE          | MORE COLS....1   | JOHN      | 2012-01-10    | ....2   | LIZA      | 2012-01-10    | ....3   | ANNY      | 2012-01-10    | ....4   | JAMES     | 2012-01-10    | .......TABLE_BID  | CODE1     | CODE2     | SORT1   | 04020     | 85003     | 11   | 04030     | 85002     | 42   | 81000     | 80703     | 13   | 87010     | 80102     | 43   | 87010     | 84701     | 54   | 04810     | 85003     | 14   | 04030     | 85002     | 44   | 04020     | 85003     | 1...QUERY RESULTID  | NAME      | DATE          | CODE1     | CODE21   | JOHN      | 2012-01-10    | 04020     | 850032   | LIZA      | 2012-01-10    | 81000     | 807033   | ANNY      | 2012-01-10    | 87010     | 801024   | JAMES     | 2012-01-10    | 04810     | 85003...

TABLE_B 中的 SORT 列实际上是重新排序的 CODE2 中的最后一个字符。 CODE2 可以以 1-9 结尾，但 3 最重要，然后是 5、7、4、2、1、0、6、8、9，因此 3-->1、5-->2、7-->3 等等

我面临的问题是我需要 TABLE_B 中的行，其中 sort 是最低的数字。在某些情况下，存在多个最小的情况(请参阅 TABLE_B 中的 ID=4)，那么选择具有最低 ID 的行中的哪一行并不重要，只是该 ID 有一个结果。

Answer 1

使用 PostgreSQL 的 DISTINCT ON 更简单、更短、更快:

SELECT DISTINCT ON (a.id)
       a.id, a.name, a.date, b.code1, b.code2
FROM   table_a a
LEFT   JOIN table_b b USING (id)
ORDER  BY a.id, b.sort;

详细信息、解释、基准和链接:

• Select first row in each GROUP BY group?

我使用了 LEFT JOIN，因此 table_a 中没有 table_b 中任何匹配行的行不会被删除。 b.sort 中产生的 NULL 值默认排在最后。

旁白:
虽然在 Postgres 中是允许的，但使用 date 作为列名是不明智的。这是一个reserved word在每个 SQL 标准中和 Postgres 中的类型名称。