Присоединитесь к одному и получите один результат

У меня есть две таблицы в PostgreSQL, если это имеет значение, с одним или несколькими отношениями. Мне нужно присоединиться к ним так, что для каждого "одного" я получаю только один результат из таблицы "многих". Не только это, но мне нужно выделить конкретные результаты из таблицы "many".

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

Столбец SORT в TABLE_B фактически является последним char в CODE2, переупорядоченным. CODE2 может заканчиваться 1-9, но 3 наиболее важно тогда 5, 7, 4, 2, 1, 0, 6, 8, 9, следовательно, 3 → 1, 5 → 2, 7 → 3 и так д.

Проблема, с которой я сталкиваюсь, заключается в том, что мне нужна строка из TABLE_B, где sort является самым низким числом. В некоторых случаях есть несколько наименьших случаев (см. ID = 4 в TABLE_B), тогда не имеет значения, какая из строк с самым низким идентификатором выбрана, только для одного идентификатора есть единственный результат.

Ответ 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. Не описательно и не полезно. Одно (из многих) возможных соглашений об именах должно было бы назвать его после таблицы, где это первичный ключ: table_a_id. То же имя для внешних ключей, ссылающихся на него (если никакое другое естественное имя не имеет приоритета).

Ответ 2

PostgreSQL поддерживает функцию окна. Попробуйте это,

SELECT d.ID, d.NAME, d.DATE, d.CODE1, d.CODE2
FROM
(
  SELECT  a.ID, a.NAME, a.DATE,
          b.CODE1, b.CODE2,
          ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY b.SORT ASC, b.CODE2 DESC) AS ROWNUM
  FROM    TableA a
          INNER JOIN TableB b
            ON a.ID = b.ID
) d
WHERE d.RowNum = 1

SQLFiddle Demo

Ответ 3

Вот что я сделал бы на SQL Server.

SELECT a.ID,
    a.NAME,
    a.DATE,
    b.CODE1,
    b.CODE2
FROM TABLE_A a
JOIN TABLE_B b
    on a.ID = b.ID
WHERE b.SORT = (SELECT MIN(SORT) 
    FROM TABLE_B
    WHERE ID = b.ID)