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

Question

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

TABLE_A
ID  | 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_B
ID  | CODE1     | CODE2     | SORT
1   | 04020     | 85003     | 1
1   | 04030     | 85002     | 4
2   | 81000     | 80703     | 1
3   | 87010     | 80102     | 4
3   | 87010     | 84701     | 5
4   | 04810     | 85003     | 1
4   | 04030     | 85002     | 4
4   | 04020     | 85003     | 1
...

QUERY RESULT
ID  | NAME      | DATE          | CODE1     | CODE2
1   | JOHN      | 2012-01-10    | 04020     | 85003
2   | LIZA      | 2012-01-10    | 81000     | 80703
3   | ANNY      | 2012-01-10    | 87010     | 80102
4   | 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

此紧密相关答案中的详细信息，解释，基准和链接。
我使用LEFT JOIN，这样不会删除table_a没有匹配行的行table_b。

旁注：

虽然在PostgreSQL中被允许，但date用作列名是不明智的。在每个SQL标准中，它都是保留字，在PsotgreSQL中，它是类型名。

命名ID列也是一种反模式id。没有描述性，也没有帮助。一种（多种）可能的命​​名约定是在表作为主键的情况下对其进行命名table_a_id。引用它的外键使用相同的名称（如果没有其他自然名称优先）。