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

У меня есть таблица media_tags, содержащая два столбца, связывающих мультимедиа с пользовательскими тегами. Я хочу сделать несколько запросов, выбирая медиа со сложными условиями для тегов. Мое текущее решение использует один и тот же коррелированный подзапрос несколько раз.

Несколько ответов SO указали мне на CTE, но, насколько я понимаю, их можно использовать только для замены некоррелированных подзапросов.

SELECT outer.media_id
FROM media_tags AS outer
WHERE 1 IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id)
AND 2 NOT IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id)
AND (3 IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id)
OR 4 IN (SELECT tag_id FROM media_tags AS inner WHERE outer.media_id = inner.media_id));
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
99
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Как насчет использования not exists?

SELECT m2.media_id
FROM media_tags mt
WHERE NOT EXISTS (SELECT tag_id
                  FROM media_tags mt2
                  WHERE mt2.media_id = mt.media_id AND
                        mt2.tag_id IN (1, 2, 3, 4)
                 );

Я не вижу способа использовать это для сложных запросов, таких как tag_1 AND NOT tag_2 AND (tag_3 OR tag_4)

Jap 22.07.2019 23:47
Ответ принят как подходящий

Вы можете выполнить самосоединение, GROUP BY media_id и установить условия в пункте HAVING:

SELECT outer.media_id
FROM media_tags AS outer INNER JOIN media_tags AS inner
ON outer.media_id = inner.media_id
GROUP BY outer.media_id
HAVING 
  SUM(inner.tag_id = 1) > 0
  AND
  SUM(inner.tag_id = 2) = 0
  AND
  SUM(inner.tag_id IN (3, 4)) > 0

или даже проще без проверки самосоединения, если это также работает:

SELECT media_id
FROM media_tags
GROUP BY media_id
HAVING 
  SUM(tag_id = 1) > 0
  AND
  SUM(tag_id = 2) = 0
  AND
  SUM(tag_id IN (3, 4)) > 0

Другие вопросы по теме