Соединение между таблицами сопоставления (соединения) с определенной кардинальностью

Вы можете создать временную таблицу, используя DECLARE GLOBAL TEMPORARY TABLE. А затем выполните ВНУТРЕННЕЕ СОЕДИНЕНИЕ, чтобы устранить «ГДЕ В». Работа с соединениями, основанными на множестве, обычно намного эффективнее, чем операторы Where, которые должны оцениваться для каждой строки.

попробуй это:

SELECT DISTINCT f.Animal_ID, g.Name
FROM Animal f INNER JOIN 
    (SELECT a.Animal_ID, a.Name, COUNT(*) as iCount
     FROM   Animal a INNER JOIN Animal_Tag b
                  ON a.Animal_ID = b.animal_ID
                     INNER JOIN Tags c
                  On b.tag_ID = c.tag_ID
    WHERE c.tag IN ('fluffy', 'brown', 'cute') -- list all tags here
    GROUP BY a.Animal_ID) g
WHERE g.iCount = 3 -- No. of tags

ОБНОВЛЕНИЕ

    SELECT DISTINCT a.Animal_ID, a.Name, COUNT(*) as iCount
    FROM    Animal a INNER JOIN Animal_Tag b
                  ON a.Animal_ID = b.animal_ID
                     INNER JOIN Tags c
                  On b.tag_ID = c.tag_ID
    WHERE c.tag IN ('fluffy', 'brown', 'cute') -- list all tags here
    GROUP BY Animal_ID
    HAVING  iCount = 3 -- No. of tags

Попробуйте это:

SELECT a.*
FROM animal a
INNER JOIN 
  ( 
    SELECT at.animal_id
    FROM tag t
    INNER JOIN animal_tag at ON at.tag_id = t.tag_id
    WHERE tag IN ('fluffy', 'brown', 'cute')
    GROUP BY at.animal_id
    HAVING count(*) = 3
  ) f ON  a.animal_id = f.animal_id

Вот еще вариант, просто для удовольствия:

SELECT a.*
FROM animal a
INNER JOIN animal_tag at1 on at1.animal_id = a.animal_id
INNER JOIN tag t1 on t1.tag_id = at1.tag_id
INNER JOIN animal_tag at2 on at2.animal_id = a.animal_id
INNER JOIN tag t2 on t2.tag_id = at2.tag_id
INNER JOIN animal_tag at3 on at3.animal_id = a.animal_id
INNER JOIN tag t3 on t3.tag_id = at3.tag_id
WHERE t1.tag = 'fluffy' AND t2.tag = 'brown' AND t3.tag = 'cute'

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

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

Хотя много лет назад я прошел курс реляционной модели данных Кодда, этот курс, как и многие другие, на самом деле не охватывал реляционное деление. Невольно мой первоначальный запрос на самом деле является приложением Relational Division.

Ссылаясь на слайд 26–27 в этой презентации, посвященный реляционному делению, мой запрос применяет метод сравнения мощностей наборов. Я попробовал некоторые другие методы, упомянутые для применения реляционного деления, но, по крайней мере, в моем случае метод подсчета обеспечивает самое быстрое время выполнения. Я призываю всех, кто интересуется этой проблемой, прочитать вышеупомянутую стопку слайдов, а также статью, на которую ссылается на этой странице Микаэль Эрикссон. Еще раз спасибо всем.

Мне было интересно, насколько плохо было бы использовать там реляционное деление. Не могли бы вы запустить его? Я знаю, что это займет больше времени, но я заинтригован тем, сколько :) Если вы можете предоставить и ориентировочную стоимость, и время, было бы здорово.

select a2.animal_id, a2.animal_name from animal2 a2
where not exists (
    select * from animal1 a1, tags t1
    where not exists (
        select * from animal_tag at1
        where at1.animal_id = a1.animal_id and at1.animal_tag = t1.tag_id
    ) and a2.animal_id = a1.animal_id and t1.tag in ('fluffy', 'brown', 'cute')
)

Теперь ищу быстрый запрос, я не могу думать быстрее, чем у Джона или у вас. На самом деле у Джона может быть немного медленнее, чем у вас, потому что он выполняет ненужные операции (удалить отдельные и удалить количество (*) из выбора):

SELECT a.Animal_ID, a.Name FROM Animal a
INNER JOIN Animal_Tag b ON a.Animal_ID = b.animal_ID
INNER JOIN Tags c On b.tag_ID = c.tag_ID
WHERE c.tag IN ('fluffy', 'brown', 'cute') -- list all tags here
GROUP BY Animal_ID, a.Name
HAVING count(*) = 3 -- No. of tags

Это должно быть так же быстро, как и у вас.

PS: Есть ли способ удалить эту чертову 3, не дублируя предложение where? Мой мозг кипит :)