MySQL 中的交集

Question

我有两个表，记录和数据。记录有多个字段（名字、姓氏等）。这些字段中的每一个都是存储实际值的数据表的外键。我需要搜索多个记录字段。

下面是一个使用 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"

感谢您的任何帮助。

Answer 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'
)

Answer 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，并且最终的环绕查询将包含它。

Answer 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');

预期结果：

2

测试数据可能对发布者没有用，但对于想要检查解决方案以确保其正常工作的选民，或者想要提交答案的人来说可能有用。他们可以测试自己的答案。

Answer 4

我来晚了一点，但我认为完全模拟 INTERSECT 的最干净、最好的方法是：

SELECT * FROM
( SELECT records.id FROM records, data WHERE data.id = records.firstname AND data.value = "john" ) x1
NATURAL JOIN
( SELECT records.id FROM records, data WHERE data.id = records.lastname AND data.value = "smith" ) x2

Answer 5

自 2022 年 11 月 10 日起，MySQL 添加了对 INTERSECT< /code>运算符与 版本8.0.31的更新。

您现在可以随意使用它：

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"

Answer 6

MYSQL 中 INTERSECT 的一般替代是内部联接：

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)

Answer 7

从表 t WHERE NOT EXISTS 中选择 t.id（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

Answer 8

由于Mysql不支持INTERSECT，因此您可能有2种选择：内连接和in。这是一个 in 的解决方案：

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);

Answer 9

你可以试试这个：

SELECT 
        r.id, GROUP_CONCAT(data.value) AS gcValue
FROM records r
LEFT JOIN data d ON d.id = records.firstname
GROUP BY records.firstname
HAVING FIND_IN_SET('john', gcValue) AND FIND_IN_SET('smith', gcValue); 

但是这个选项没有优化。