MySQL MySQL中的相交
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2300322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INTERSECT in MySQL
提问by
I have two tables, records and data. records has multiple fields (firstname, lastname, etc.). Each of these fields is a foreign key for the data table where the actual value is stored. I need to search on multiple record fields.
我有两个表，记录和数据。记录有多个字段（名字、姓氏等）。这些字段中的每一个都是存储实际值的数据表的外键。我需要搜索多个记录字段。
Below is an example query using INTERSECT, but I need one that works in 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"
Thanks for any help.
谢谢你的帮助。
回答by Andomar
You can use an inner join to filter for rows that have a matching row in another table:
您可以使用内部联接来过滤在另一个表中具有匹配行的行：
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"
One of many other alternatives is an inclause:
许多其他选择之一是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'
)
回答by Enigma Plus
I think this method is much easier to follow, but there is a bit of an overhead associated with it because you are loading up lots of duplicate records initially. I use it on a database with about 10000-50000 records and typically intersect about 5 queries and the performance is acceptable.
我认为这种方法更容易遵循，但有一些与之相关的开销，因为您最初加载了大量重复记录。我在一个有大约 10000-50000 条记录的数据库上使用它，通常会交叉大约 5 个查询，性能是可以接受的。
All you do is "UNION ALL" each of the queries you want to intersect and see which ones you got every time.
你所做的就是“联合所有”你想要相交的每个查询，看看你每次得到哪些。
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
So if we get the same record in both queries, it's count will be 2 and the final wrap-around query will include it.
所以如果我们在两个查询中得到相同的记录，它的计数将为 2，最终的环绕查询将包括它。
回答by Mark Byers
Use joins instead:
改用连接：
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'
Here's some test data:
下面是一些测试数据：
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');
Expected result:
预期结果：
2
The test data is probably not useful for the poster, but might be useful for voters who want to check solutions to see that they work correctly, or people who want to submit answers so that they can test their own answers.
测试数据可能对海报没有用，但对于想要检查解决方案以查看它们是否正常工作的选民或想要提交答案以便他们可以测试自己的答案的人可能有用。
回答by Will Hay
A general replacement for INTERSECT in MYSQL is inner join:
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)
Or for your case specifically:
或者专门针对您的情况：
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)
回答by Nazar Maksymchuk
SELECT t.id FROM table t WHERE NOT EXISTS (SELECT t2.id, FROM table2 t2 WHERE t2.id = t1.id)
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
https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html
回答by vinhNguyen.bmt
Since Mysql doesn't support INTERSECT, you may have 2 alternatives: inner joinand in. This is a solution with in:
由于 Mysql 不支持 INTERSECT，您可能有两种选择：内连接和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);
