Хобрук: Ваш путь к мастерству в программировании

SQL-запрос для сопоставления записей с/без всех определенных совпадений «многие ко многим»

У меня есть три таблицы: posts, tags и postTags. Как вы, наверное, догадались, posts содержит информацию о сообщениях в блогах, tags содержит информацию о тех тегах, которые используются в системе, а postTags содержит отношения между posts и tags.

Теперь давайте предположим, что я знаю tagID каждого тега, который я ищу, а также те, которые я не знаю, что было бы подходящим, что было бы подходящим запросом для получения всех posts, которые соответствуют критериям наличия всех tagIDs I указать в одном списке и не иметь ничего из того, что я указываю в другом?

Один из способов, которым я могу работать, это:

SELECT
    `posts`.*,
    CONCAT(',', GROUP_CONCAT(`postTags`.`tagID`), ',') AS `pTags`
FROM
    `posts`
INNER JOIN
    `postTags`
    ON
    `postTags`.`postID` = `posts`.`postID`
GROUP BY
    `posts`.`postID`
HAVING
    `pTags` LIKE '%,2,%'
    AND
    `pTags` LIKE '%,3,%'
    AND
    `pTags` NOT LIKE '%,5,%'

Этот запрос выберет все сообщения, которые были помечены tagID 2 и 3, а не помечены tagID 5. Но это кажется потенциально довольно медленным, особенно когда данные фильтруются большим количеством тегов.

ИЗМЕНИТЬ

скрипт SQL

mysql sql select many-to-many
23.05.2013

Ответы:


1

Вы можете попробовать оптимизировать запрос с помощью EXISTS Strategy:

SELECT
    `posts`.*
FROM
    `posts`
WHERE
    EXISTS (
      SELECT 1 FROM `postTags` 
      WHERE `postTags`.`postID` = `posts`.`postID`
        AND `postTags`.`tagID` = 2
    )
    AND
    EXISTS (
      SELECT 1 FROM `postTags` 
      WHERE `postTags`.`postID` = `posts`.`postID`
        AND `postTags`.`tagID` = 3
    )    
    AND NOT EXISTS (
      SELECT 1 FROM `postTags` 
      WHERE `postTags`.`postID` = `posts`.`postID`
        AND `postTags`.`tagID` = 5
    )
23.05.2013

2

Я бы сделал соединения с парой подзапросов, избегая коррелированных подзапросов.

Что-то вроде следующего (не уверен, что вам нужен объединенный список тегов в SELECT, но пока оставил его там)

SELECT `posts`.*,
    CONCAT(',', Sub1.TagList, ',') AS `pTags`
FROM `posts`
INNER JOIN (
    SELECT postID, GROUP_CONCAT(`postTags`.`tagID`) AS TagList, COUNT(*) AS TagCount 
    FROM postTags 
    WHERE tagID IN (2, 3) 
    GROUP BY postID 
    HAVING TagCount = 2
) Sub1
ON posts.postID = Sub1.postID
LEFT OUTER JOIN (
    SELECT postID
    FROM postTags 
    WHERE tagID IN (5) 
) Sub2
ON posts.postID = Sub2.postID
WHERE Sub2.postID IS NULL
23.05.2013
  • Ой, мне нравится, очень прикольно 23.05.2013

    • 3

    Я считаю, что это принесет сообщения, которые вы хотите.

    SELECT 
    p.*, GROUP_CONCAT(pt.tagID)    
FROM
    posts p
    inner join postTags pt on p.postID  = pt.postID
WHERE
not exists (
    SELECT
        1
    FROM
        Tags t
    WHERE
        t.tagID in (2,3)
        AND not exists
        (
           select 1 from postTags pt where pt.postID = p.postID and pt.tagID = t.tagID
        )
)
and not exists(
    SELECT
        1
    FROM
        Tags t
    WHERE
        t.tagID in (5)
        AND exists
        (
           select 1 from postTags pt where pt.postID = p.postID and pt.tagID = t.tagID
        )
)
GROUP BY p.postID
    03.11.2014
    Новые материалы
    Создание кнопочного меню с использованием HTML, CSS и JavaScript
    Вы будете создавать кнопочное меню, которое имеет состояние наведения, а также позволяет вам выбирать кнопку при нажатии на нее. Финальный проект можно увидеть в этом Codepen . Шаг 1..
    Внедрите OAuth в свои веб-приложения для повышения безопасности
    OAuth — это широко распространенный стандарт авторизации, который позволяет приложениям получать доступ к ресурсам от имени пользователя, не раскрывая его пароль. Это позволяет пользователям..
    Классы в JavaScript
    class является образцом java Script Object. Конструкция «class» позволяет определять классы на основе прототипов с чистым, красивым синтаксисом. // define class Human class Human {..
    Как свинг-трейдеры могут использовать ИИ для больших выигрышей
    По мере того как все больше и больше профессиональных трейдеров и активных розничных трейдеров узнают о возможностях, которые предоставляет искусственный интеллект и машинное обучение для улучшения..
    Как построить любой стол
    Я разработчик программного обеспечения. Я люблю делать вещи и всегда любил. Для меня программирование всегда было способом создавать вещи, используя только компьютер и мое воображение...
    Обзор: Машинное обучение: классификация
    Только что закончил третий курс курса 4 часть специализации по машинному обучению . Как и второй курс, он был посвящен низкоуровневой работе алгоритмов машинного обучения. Что касается..
    Разработка расширений Qlik Sense с qExt
    Использование современных инструментов веб-разработки для разработки крутых расширений Вы когда-нибудь хотели кнопку для установки переменной в приложении Qlik Sense? Когда-нибудь просили..