INTERSECT в MySQL

У меня есть две таблицы, записи и данные. записи имеют несколько полей (имя, фамилия и т.д.). Каждое из этих полей является внешним ключом для таблицы данных, в которой хранится фактическое значение. Мне нужно выполнить поиск по нескольким полям записи.

Ниже приведен пример запроса с использованием INTERSECT, но мне нужен тот, который работает в MySQL.

SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john"
INTERSECT
SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith"

Спасибо за любую помощь.

Ответ 1

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

SELECT DISTINCT records.id 
FROM records
INNER JOIN data d1 on d1.id = records.firstname AND data.value = "john"
INNER JOIN data d2 on d2.id = records.lastname AND data.value = "smith"

Один из многих других альтернатив - это предложение in:

SELECT DISTINCT records.id 
FROM records
WHERE records.firstname IN (
    select id from data where value = 'john'
) AND records.lastname IN (
    select id from data where value = 'smith'
)

Ответ 2

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

Все, что вы делаете, это "UNION ALL", каждый из запросов, которые вы хотите пересечь, и посмотреть, какие из них вы получили каждый раз.

SELECT * From (

    (Select data1.* From data1 Inner Join data2 on data1.id=data2.id where data2.something=true)
    Union All
    (Select data1.* From data1 Inner Join data3 on data1.id=data3.id where data3.something=false)

) As tbl GROUP BY tbl.ID HAVING COUNT(*)=2

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

Ответ 3

Вместо этого используйте объединения:

SELECT records.id
FROM records
JOIN data AS D1 ON records.firstname = D1.id
JOIN data AS D2 ON records.lastname = D2.id
WHERE D1.value = 'john' and D2.value = 'smith'

Вот некоторые тестовые данные:

CREATE TABLE records (id INT NOT NULL, firstname INT NOT NULL, lastname INT NOT NULL);
INSERT INTO records (id, firstname, lastname) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 2, 2);

CREATE TABLE data (id INT NOT NULL, value NVARCHAR(100) NOT NULL);
INSERT INTO data (id, value) VALUES
(1, 'john'),
(2, 'smith');

Ожидаемый результат:

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

Ответ 4

Общая замена для INTERSECT в MYSQL - это внутреннее соединение:

SELECT DISTINCT * FROM 
(SELECT f1, f2, f3... FROM table1 WHERE f1>0)
INNER JOIN
(SELECT f1, f2, f3... FROM table2 WHERE f1>0)
USING(primary_key)

Или для вашего случая:

SELECT DISTINCT * FROM 
(SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john") query1
INNER JOIN
(SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith") query2
USING (id)

Ответ 5

SELECT t.id FROM table t WHERE NOT EXISTS (SELECT t2.id, FROM table2 t2 WHERE t2.id = t1.id)

https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

Ответ 6

Так как Mysql не поддерживает INTERSECT, вы можете иметь 2 варианта: внутреннее соединение и. Это решение с учетом:

SELECT records.id FROM records, data 
WHERE data.id = records.firstname AND data.value = "john"
    AND records.id in (SELECT records.id FROM records, data 
    WHERE data.id = records.lastname AND data.value = "smith);