Nano Hash - криптовалюты, майнинг, программирование

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

У меня есть две таблицы в 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
...

Столбец SORT в TABLE_B фактически является последним переупорядоченным символом в 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), тогда не имеет значения, какая из строк с самым низким идентификатором выбрана, только то, что для этого идентификатора есть один результат.

postgresql sql join select one-to-many
17.09.2012

  • Добро пожаловать в StackOverflow! Спасибо, что показали данные и написали четкий вопрос. Однако в следующий раз вы облегчите жизнь тем, кто захочет вам помочь, если вы покажете свои данные в терминах операторов CREATE TABLE и операторов INSERT или COPY для загрузки данных. Таким образом, люди могут легко проверить варианты ответов перед публикацией, чтобы убедиться, что они не имеют синтаксических ошибок и получают желаемые результаты. 18.09.2012
  • Спасибо за ваш комментарий. Я буду делать это с этого момента. 31.01.2014

Ответы:


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

18.09.2012
  • Спасибо, это решение тоже работает. Именование столбцов было исключительно описательным для этого примера. 18.09.2012
  • @thorgilsv: я понимаю. Эта версия должна быть немного быстрее. Вы можете протестировать с помощью EXPLAIN ANALYZE. 18.09.2012

    • 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

    17.09.2012
  • Спасибо, но это дает мне тот же результат, как если бы я соединил все из TABLE_B в TABLE_A, то есть многие к одному. 18.09.2012
  • @user1678791 user1678791, поскольку вы добавили тег postgresql, я обновил ответ, используя window function 18.09.2012
  • Эврика! Большое спасибо, это решение моего вопроса. +1 за демонстрацию SQLFiddle, просто фантастика. 18.09.2012
  • Кажется, скрипка больше не дает правильного результата 30.12.2017

    • 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)
    18.09.2012
    Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..