Arhn - архитектура программирования

SQL Выберите только строки, в которых существует несколько отношений

Учитывая родительскую таблицу 'parent'

╔═══════════╦══════════╗
║ PARENT_ID ║   NAME   ║
╠═══════════╬══════════╣
║         1 ║ bob      ║
║         2 ║ carol    ║
║         3 ║ stew     ║
╚═══════════╩══════════╝

и таблица отношений "многие-многие" между родительским элементом и (здесь не указана) таблица свойств

╔═══════════╦═══════════╗
║ PARENT_ID ║  PROP_ID  ║
╠═══════════╬═══════════╣
║         1 ║         5 ║
║         1 ║         1 ║
║         2 ║         5 ║
║         2 ║         4 ║
║         2 ║         1 ║
║         3 ║         1 ║
║         3 ║         3 ║
╚═══════════╩═══════════╝

Как я могу выбрать всех родителей, у которых есть все из указанного набора родственных связей? Например. с образцами данных, как мне найти всех родителей, у которых есть свойство 5 и 1?

изменить: тот же вопрос, но с требованием для точного совпадения: SQL Выбирать только строки, в которых существует несколько точных взаимосвязей

mysql sql select
31.12.2012

Ответы:


1

Это называется Отдел отношений

SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(*) = 2

ОБНОВЛЕНИЕ 1

если ограничение уникальности не применялось для prop_id для каждого parent_id, в этом случае требуется DISTINCT.

SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(DISTINCT b.prop_id) = 2
31.12.2012
  • Это предполагает, что комбинация parent_id и prop_id всегда уникальна. Вы можете изменить предложение HAVING, чтобы возвращать более точно: HAVING COUNT(DISTINCT b.prop_id) = 2 31.12.2012
  • @Nicarus да, как показали примеры записей. но если они не уникальны, если предполагается, что в таблице есть другое поле, которое делает столбцы уникальными, тогда требуется ключевое слово DISTINCT. 31.12.2012
  • Мне любопытно - зачем нужна оговорка о «подсчете»? 31.12.2012
  • количество записей, возвращаемых по запросу, равно количеству значений в условии, попробуйте выполнить это, SELECT a.name FROM parent a INNER JOIN rel b ON a.parent_ID = b.parent_ID WHERE b.prop_id IN (1,5), и увидите разницу. 31.12.2012
  • Я понимаю, что пропустил здесь кое-что. Размещено дополнительное требование в разделе: stackoverflow.com/questions/14113267/ 01.01.2013

    • 2

    Я только что увидел это решение по другому вопросу, который, кажется, подходит для этого случая:

     SELECT distinct parent_id
 FROM rel as T1
 INNER JOIN rel as T2
 ON T1.parent_id = T2.parent_id
 WHERE T1.prop_id = '1' and T2.prop_id = '5'
    31.12.2012
  • что, если вам нужно найти другого prop_id? тебе снова нужно еще одно соединение? трата ресурсов. 31.12.2012
  • Это может быть пустой тратой вычислительных ресурсов, однако ответ полностью верен и может сэкономить много ресурсов мышления для программиста, поэтому стоит подумать о нем. Не оптимизируйте преждевременно! 06.12.2017

    • 3

    Я записал вашу таблицу в CTE, дайте мне знать, если вам потребуется помощь в адаптации кода для ваших целей.

    ;WITH MyTable AS
(
    SELECT   parent_id = 1
            ,prop_id = 5    UNION ALL
    SELECT 1,1              UNION ALL
    SELECT 2,5              UNION ALL
    SELECT 2,4              UNION ALL
    SELECT 2,1              UNION ALL
    SELECT 3,1              UNION ALL
    SELECT 3,3              
)
,Eval AS
(
    SELECT   parent_id
            ,PropEval   = SUM(CASE WHEN prop_id IN (1,5) THEN 1 ELSE 0 END)
    FROM MyTable
    GROUP BY parent_id
)
SELECT parent_id
FROM Eval
WHERE PropEval = 2
    31.12.2012
  • mysql, к сожалению, не поддерживает cte. 31.12.2012
  • Мне очень жаль, я не понял, что это только MySQL. Виноват. 31.12.2012

    • 4

    назовите свою первую таблицу a, а вторую таблицу b

    SELECT parent_id FROM prop b1 
WHERE prop_id=1 and 
EXISTS (SELECT parent_id FROM prop b2 
        WHERE b2.parent_id=b1.parent_id AND b2.prop_id=5)
    31.12.2012
    Новые материалы
    Коллекции публикаций по глубокому обучению
    Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..
    Представляем: Pepita
    Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..
    Советы по коду Laravel #2
    1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..
    Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)
    Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..
    3 способа решить квадратное уравнение (3-й мой любимый) -
    1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..
    Создание VR-миров с A-Frame
    Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..
    Демистификация рекурсии
    КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..