一对多加入并检索单个结果 [英] Join one to many and retrieve single result

问题描述

我有两个表(如果有关系的话)在PostgreSQL中，具有一对多关系.我需要加入它们，以便对于每个一个"，我只能从许多"表中获得单个结果.不仅如此，我还需要从许多"表中挑选出具体结果.

I have two tables, in PostgreSQL if that matters, with one to many relations. I need to join them so that for each "one" I only get single result from the "many" table. Not only that but I need to single out specific results from the "many" table.

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等等

The SORT column in TABLE_B is actually the last char in CODE2 reordered. CODE2 can end with 1-9 but 3 is most important then 5, 7, 4, 2, 1, 0, 6, 8, 9 hence 3-->1, 5-->2, 7-->3 and so forth.

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

The problem I'm facing is that I need the row from TABLE_B where sort is the lowest number. In some cases there are multiple lowest case (see ID=4 in TABLE_B) then it doesn't matter which of the rows with lowest ID are selected, only that there is single result for that ID.

推荐答案

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

Simpler, shorter, faster with PostgreSQL's 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_a行.

Details, explanation, benchmark and links in this closely related answer.
I use a LEFT JOIN, so that rows from table_a without any matching row in table_b are not dropped.

旁注:

虽然在PostgreSQL中被允许，但是使用date作为列名是不明智的.在每个SQL标准中，它都是保留字，在PsotgreSQL中输入名称.

While being allowed in PostgreSQL, it's unwise to use date as column name. It's a reserved word in every SQL standard and a type name in PsotgreSQL.

命名ID列id也是一种反模式.没有描述性，也没有帮助. (很多)可能的命​​名约定是在它作为主键的表后命名:table_a_id.引用它的外键名称相同(如果没有其他自然名称优先).

It's also an anti-pattern to name an ID column id. Not descriptive and not helpful. One (of many) possible naming convention would be to name it after the table where it is primary key: table_a_id. Same name for foreign keys referencing it (if no other natural name takes precedence).

这篇关于一对多加入并检索单个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！