Подтвердить что ты не робот

SQL для применения условий к нескольким строкам в соединении

Я думаю, что нашел ответ на свой вопрос, я просто не уверен в синтаксисе, я продолжаю получать ошибки SQL.

В принципе, я хочу сделать противоположное IN. Возьмите этот пример:

SELECT * 
  FROM users INNER JOIN 
       tags ON tags.user_id = users.id 
 WHERE tags.name IN ('tag1', 'tag2');

Приведенное выше вернет ЛЮБЫХ пользователей, у которых есть "tag1" ИЛИ "tag2". Я хочу, чтобы пользователи имели ОБА. У них должны быть возвращены теги. Я предполагаю, что ключевое слово ALL должно использоваться, но не может заставить его работать.

Спасибо за вашу помощь.

4b9b3361

ОТВЕТЫ

Ответ 1

Вы снова захотите присоединиться к таблице тегов.

SELECT * FROM users
INNER JOIN tags as t1 on t1.user_id = users.id and t1.name='tag1'
INNER JOIN tags as t2 on t2.user_id = users.id and t2.name='tag2'

Ответ 2

Сначала поговорим об этой проблеме в общих чертах, а затем о специфике.

В этой задаче вы хотите выбрать строки из таблицы A в зависимости от условий в двух (или для общего случая, более двух) строк в таблице B. Для этого вам нужно сделать один из две вещи:

  • выполнить тесты против разных строк в таблице B

  • агрегировать интересующие строки из таблицы B в одну строку, которая каким-то образом содержит информацию, необходимую для проверки исходных строк из таблицы B

Эта проблема является большой причиной, по которой, я думаю, вы видите, что люди создают списки с разделителями-запятыми в полях VARCHAR, а не нормализуют их базы данных правильно.

В вашем примере вы хотите выбрать строки user на основе существования строк, соответствующих двум конкретным условиям в tags.

(1) Тестирование разных строк.

Существует три способа использования техники (1) (тестирование разных строк). Они используют EXISTS, используя подзапросы и используя JOINs:

1A. Использование EXISTs (по-моему, так или иначе) ясно, потому что оно соответствует тому, что вы пытаетесь сделать - проверке наличия строк. Это умеренно масштабируемо для большего количества тегов с точки зрения написания создания SQL, если вы создаете динамический SQL, вы просто добавляете дополнительное предложение AND EXISTS для каждого тега (производительность, конечно же, будет страдать):

SELECT * FROM users WHERE 
  EXISTS (SELECT * FROM tags WHERE user_id = users.id AND name ='tag1') AND
  EXISTS (SELECT * FROM tags WHERE user_id = users.id AND name ='tag2')

Я думаю, что это четко выражает намерение запроса.

1B Использование подзапросов также довольно ясно. Поскольку этот метод не связан с коррелированными подзапросами, некоторые двигатели могут оптимизировать его лучше (отчасти это зависит от количества пользователей с любым заданным тегом):

SELECT * FROM users WHERE 
  id IN (SELECT user_id FROM tags WHERE name ='tag1') AND
  id IN (SELECT user_id FROM tags WHERE name ='tag2')

Это масштабируется так же, как и вариант 1A. Это также (для меня, во всяком случае) довольно понятно.

1C Использование JOINs включает INNER JOINING в таблицу тегов в таблицу пользователей один раз для каждого тега. Он не масштабируется, потому что он сложнее (но все же возможно) для генерации динамического SQL:

SELECT u.* FROM users u 
     INNER JOIN tags t1 ON u.id = t1.user_id
     INNER JOIN tags t2 ON u.id = t2.user_id
  WHERE t1.name = 'tag1' AND t2.name = 'tag2'

Лично я чувствую, что это значительно менее понятно, чем два других варианта, поскольку похоже, что целью является создание набора записей JOINed, а не фильтрация таблицы пользователей. Кроме того, масштабируемость страдает, потому что вам нужно добавить INNER JOINs и изменить предложение WHERE. Обратите внимание, что этот метод похож на методы 1 и 2 оседлости, потому что он использует JOINs для агрегирования двух строк из тегов.

(2) Объединение строк.

Есть два основных способа сделать это, используя COUNT и используя обработку строк:

2A Использование COUNT намного проще, если ваша таблица тегов "защищена" от того, что тот же тег дважды применяется к тому же пользователю. Вы можете сделать это, сделав (user_id, name) PRIMARY KEY в тегах или создав УНИКАЛЬНЫЙ ИНДЕКС на этих двух столбцах. Если строки защищены таким образом, вы можете сделать это:

 SELECT users.id, users.user_name 
   FROM users INNER JOIN tags ON users.id = tags.user_id
   WHERE tags.name IN ('tag1', 'tag2')
   GROUP BY users.id, users.user_name
   HAVING COUNT(*) = 2

В этом случае вы сопоставляете значение HAVING COUNT (*) = test с числом имен тегов в предложении IN. Это не работает, если каждый тэг можно применить к пользователю более одного раза, потому что счетчик 2 может быть создан двумя экземплярами "tag1", а ни один из "tag2" (а строка не будет соответствовать тому, где он не должен) или два экземпляра "tag1" плюс один экземпляр "tag2" создавали бы счет 3 (и пользователь не получил бы квалификацию, даже если они должны были).

Обратите внимание, что это самый масштабируемый метод с точки зрения производительности, поскольку вы можете добавлять дополнительные теги и не требуются дополнительные запросы или JOIN.

Если разрешено несколько тегов, вы можете выполнить внутреннюю агрегацию для удаления дубликатов. Вы можете сделать это в том же запросе, который я показал выше, но для простоты я собираюсь разбить логику на отдельный вид:

 CREATE VIEW tags_dedup (user_id, name) AS
 SELECT DISTINCT user_id, name FROM tags

а затем вернитесь к указанному выше запросу и замените tags_dedup для тегов.

2B Использование обработки строк - это спецификация базы данных, потому что нет стандартной функции агрегации SQL для создания строковых списков из нескольких строк. Однако некоторые базы данных предлагают расширения для этого. В MySQL вы можете использовать GROUP_CONCAT и FIND_IN_SET для этого:

SELECT user.id, users.user_name, GROUP_CONCAT(tags.name) as all_tags
  FROM users INNER JOIN tags ON users.id = tags.user_id
  GROUP BY users.id, users.user_name
  HAVING FIND_IN_SET('tag1', all_tags) > 0 AND
         FIND_IN_SET('tag2', all_tags) > 0

Обратите внимание: это очень неэффективно и использует уникальные расширения MySQL.

Ответ 3

Я бы сделал именно то, что вы делаете первым, потому что это получает список всех пользователей с "tag1" и список всех пользователей с "tag2", но в этом же ответе явно. Итак, мы должны добавить еще несколько:

Сделайте a group by users (или users.id), а затем having count(*) == 2. Это приведет к группированию дублированных пользователей (что означает те, у которых есть тег1 и tag2), а затем часть-получатель удалит их только одним из двух тегов.

Это решение избегает добавления еще одного заявления о соединении, но, честно говоря, я не уверен, что быстрее. Люди, не стесняйтесь прокомментировать часть исполнения:)

РЕДАКТИРОВАТЬ: просто чтобы было проще попробовать, вот все:

SELECT * 
FROM users INNER JOIN 
     tags ON tags.user_id = users.id 
WHERE tags.name = 'tag1' OR tags.name = 'tag2'
GROUP BY users.id
HAVING COUNT(*) = 2

Ответ 4

Хорошо, снова задав проблему.

"Найдите пользователей, у которых есть записи в таблице тегов для tag1 и tag2". Это означает, что по крайней мере 2 строки в таблице дочерних тегов для каждой записи пользовательской таблицы

Решение 1: пересечение "пользователей с tag1" и "пользователи с tag2" 

SELECT u.*
FROM 
    users u INNER JOIN 
    (
    SELECT user_id FROM tags WHERE name = 'tag1'
    INTERSECT
    SELECT user_id FROM tags WHERE name = 'tag2'
    ) t ON u.id = t.user_id

Решение 2: EXISTS

SELECT u.*
FROM 
    users u
WHERE 
    EXISTS (SELECT * FROM tags t1 WHERE t1.name = 'tag1'
                     AND u.id = t1.user_id)
    AND
    EXISTS (SELECT * FROM tags t2 WHERE t2.name = 'tag2'
                     AND u.id = t2.user_id)

Решение 3: JOIN

SELECT u.* FROM
   users u
   INNER JOIN
   tags as t1 on t1.user_id = u.id
   INNER JOIN
   tags as t2 on t2.user_id = u.id 
WHERE
   t1.name='tag1' AND t2.name='tag2'

Решение 4: IN

SELECT u.*
FROM 
    users u
WHERE 
    u.id (SELECT t1.user_id FROM tags t1 WHERE t1.name = 'tag1')
    AND
    u.id (SELECT t2.user_id FROM tags t2 WHERE t2.name = 'tag2')

Все EXISTS, INTERSECT и IN должны предоставить тот же план выполнения в SQL Server

Теперь все это для случая, когда вы ищете 2 тега. Поскольку вам нужно больше тегов, они становятся громоздкими, поэтому используйте решение shahkalpesh.

Однако я бы изменил его, чтобы теги находились в таблице, и никаких дополнительных предложений OR не требуется

SELECT u.*
FROM
    Users u
    Inner join
    tags t ON t.user_id = u.id
    JOIN
    @MyTags mt ON t.name = mt.name
GROUP BY u.*
HAVING count(tags.*) = COUNT(DISTINCT mt.name)

Ответ 5

SELECT Users.id, count(tags.*) as tagCount
FROM Users Inner join tags
ON tags.user_id = users.id
WHERE tags.name='tag1' OR tags.name='tag2'
GROUP BY Users.id
HAVING count(tags.*) = 2

Ответ 6

Попробуйте следующее:

SELECT * 
FROM users u, tags t1, tags t2
WHERE t1.user_id = t2.user_id
AND t1.name = 'tag1'
AND t2.name = 'tag2'
AND t1.user_id = u.id

Очевидно, что для большого количества тегов производительность этого запроса будет сильно ухудшена.

Ответ 7

select * from users u
where 2 = (select count(*) from tags t where t.user_id = u.id and name in ('tag1','tag2'))

Предполагая, что любой данный тег может присутствовать только один раз для пользователя.

Ответ 8

Попробуйте

SELECT *
FROM users
INNER JOIN tags ON tags.user_id = users.id
WHERE users.id in
    (
    SELECT user_id
    FROM tags
    WHERE name IN ('tag1', 'tag2')
    GROUP BY user_id
    HAVING COUNT(*) = 2
    )

Ответ 9

Вам нужно будет проверить наличие двух строк, вместо того, чтобы делать простой IN (который будет проверять только значения в каждой объединенной записи). Может быть, что-то вроде:

SELECT * 
from users
WHERE EXISTS (SELECT NULL FROM tags WHERE tags.user_id = users.id AND tags.name = 'tag1')
  AND EXISTS (SELECT NULL FROM tags WHERE tags.user_id = users.id AND tags.name = 'tag2');

Ответ 10

Что насчет

SELECT * FROM users, tags WHERE tags.user_id = users.user_id AND tags.name = 'tag1'
INTERSECT
SELECT * FROM users, tags WHERE tags.user_id = users.user_id AND tags.name = 'tag2'

Ответ 11

Try   WHERE tags.name IN ('tag1') and tags.name IN ('tag2');

Не очень эффективный, но, вероятно, один из многих способов.