在 MySQL 中相交的替代方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2621382/
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
Alternative to Intersect in MySQL
提问by Ankur Jariwala
I need to implement the following query in MySQL.
我需要在 MySQL 中实现以下查询。
(select * from emovis_reporting where (id=3 and cut_name= '全プロセス' and cut_name='恐慌') )
intersect
( select * from emovis_reporting where (id=3) and ( cut_name='全プロセス' or cut_name='恐慌') )
I know that intersect is not in MySQL. So I need another way. Please guide me.
我知道相交不在 MySQL 中。所以我需要另一种方式。请指导我。
回答by Mike
Microsoft SQL Server's INTERSECT"returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand"This is different from a standard INNER JOINor WHERE EXISTSquery.
Microsoft SQL Server 的“返回由 INTERSECT 操作数的左侧和右侧的查询返回的任何不同值”这与标准或查询不同。INTERSECTINNER JOINWHERE EXISTS
SQL Server
数据库服务器
CREATE TABLE table_a (
id INT PRIMARY KEY,
value VARCHAR(255)
);
CREATE TABLE table_b (
id INT PRIMARY KEY,
value VARCHAR(255)
);
INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO table_b VALUES (1, 'B');
SELECT value FROM table_a
INTERSECT
SELECT value FROM table_b
value
-----
B
(1 rows affected)
MySQL
MySQL
CREATE TABLE `table_a` (
`id` INT NOT NULL AUTO_INCREMENT,
`value` varchar(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `table_b` LIKE `table_a`;
INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B');
INSERT INTO table_b VALUES (1, 'B');
SELECT value FROM table_a
INNER JOIN table_b
USING (value);
+-------+
| value |
+-------+
| B |
| B |
+-------+
2 rows in set (0.00 sec)
SELECT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);
+-------+
| value |
+-------+
| B |
| B |
+-------+
With this particular question, the id column is involved, so duplicate values will not be returned, but for the sake of completeness, here's a MySQL alternative using INNER JOINand DISTINCT:
对于这个特定问题，涉及 id 列，因此不会返回重复值，但为了完整起见，这里是使用INNER JOINand的 MySQL 替代方案DISTINCT：
SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);
+-------+
| value |
+-------+
| B |
+-------+
And another example using WHERE ... INand DISTINCT:
另一个使用WHERE ... INand 的例子DISTINCT：
SELECT DISTINCT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);
+-------+
| value |
+-------+
| B |
+-------+
回答by FBB
There is a more effective way of generating an intersect, by using UNION ALL and GROUP BY. Performances are twice better according to my tests on large datasets.
通过使用 UNION ALL 和 GROUP BY，有一种更有效的生成相交的方法。根据我对大型数据集的测试，性能提高了两倍。
Example:
例子：
SELECT t1.value from (
(SELECT DISTINCT value FROM table_a)
UNION ALL
(SELECT DISTINCT value FROM table_b)
) AS t1 GROUP BY value HAVING count(*) >= 2;
It is more effective, because with the INNER JOIN solution, MySQL will look up for the results of the first query, then for each row, look up for the result in the second query. With the UNION ALL-GROUP BY solution, it will query results of the first query, results of the second query, then group the results all together at once.
它更有效，因为使用 INNER JOIN 解决方案，MySQL 将查找第一个查询的结果，然后对于每一行，在第二个查询中查找结果。使用 UNION ALL-GROUP BY 解决方案，它将查询第一个查询的结果，第二个查询的结果，然后一次将所有结果组合在一起。
回答by Quassnoi
Your query would always return an empty recordset since cut_name= '全プロセス' and cut_name='恐慌'will never evaluate to true.
您的查询将始终返回一个空记录集，因为cut_name= '全プロセス' and cut_name='恐慌'永远不会评估为true。
In general, INTERSECTin MySQLshould be emulated like this:
一般情况下，INTERSECT在MySQL应该仿效这样的：
SELECT *
FROM mytable m
WHERE EXISTS
(
SELECT NULL
FROM othertable o
WHERE (o.col1 = m.col1 OR (m.col1 IS NULL AND o.col1 IS NULL))
AND (o.col2 = m.col2 OR (m.col2 IS NULL AND o.col2 IS NULL))
AND (o.col3 = m.col3 OR (m.col3 IS NULL AND o.col3 IS NULL))
)
If both your tables have columns marked as NOT NULL, you can omit the IS NULLparts and rewrite the query with a slightly more efficient IN:
如果您的两个表都有标记为 的列NOT NULL，您可以省略这些IS NULL部分并使用稍微更有效的方式重写查询IN：
SELECT *
FROM mytable m
WHERE (col1, col2, col3) IN
(
SELECT col1, col2, col3
FROM othertable o
)
回答by Payam
I just checked it in MySQL 5.7 and am really surprised how no one offered a simple answer: NATURAL JOIN
我刚刚在 MySQL 5.7 中检查了它，我真的很惊讶没有人提供一个简单的答案：NATURAL JOIN
When the tables or (select outcome) have IDENTICAL columns, you can use NATURAL JOIN as a way to find intersection:
当表或（选择结果）具有 IDENTICAL 列时，您可以使用 NATURAL JOIN 作为查找交集的方法：
For example:
例如：
table1:
表1：
id, name, jobid
身号、姓名、工作号
'1', 'John', '1'
“1”、“约翰”、“1”
'2', 'Hyman', '3'
“2”、“Hyman”、“3”
'3', 'Adam', '2'
“3”、“亚当”、“2”
'4', 'Bill', '6'
“4”、“比尔”、“6”
table2:
表2：
id, name, jobid
身号、姓名、工作号
'1', 'John', '1'
“1”、“约翰”、“1”
'2', 'Hyman', '3'
“2”、“Hyman”、“3”
'3', 'Adam', '2'
“3”、“亚当”、“2”
'4', 'Bill', '5'
“4”、“比尔”、“5”
'5', 'Max', '6'
“5”、“最大”、“6”
And here is the query:
这是查询：
SELECT * FROM table1 NATURAL JOIN table2;
Query Result:id, name, jobid
查询结果：id、name、jobid
'1', 'John', '1'
“1”、“约翰”、“1”
'2', 'Hyman', '3'
“2”、“Hyman”、“3”
'3', 'Adam', '2'
“3”、“亚当”、“2”
回答by RobM
For completeness here is another method for emulating INTERSECT. Note that the IN (SELECT ...)form suggested in other answers is generally more efficient.
为了完整起见，这里是另一种模拟INTERSECT. 请注意，IN (SELECT ...)其他答案中建议的形式通常更有效。
Generally for a table called mytablewith a primary key called id:
通常对于一个名为mytable的主键的表id：
SELECT id
FROM mytable AS a
INNER JOIN mytable AS b ON a.id = b.id
WHERE
(a.col1 = "someval")
AND
(b.col1 = "someotherval")
(Note that if you use SELECT *with this query you will get twice as many columns as are defined in mytable, this is because INNER JOINgenerates a Cartesian product)
（请注意，如果您使用SELECT *此查询，您将获得两倍于 中定义的列mytable，这是因为INNER JOIN生成笛卡尔积）
The INNER JOINhere generates every permutationof row-pairs from your table. That means every combination of rows is generated, in every possible order. The WHEREclause then filters the aside of the pair, then the bside. The result is that only rows which satisfy both conditions are returned, just like intersection two queries would do.
在INNER JOIN这里产生每置换从表中的行对。这意味着以各种可能的顺序生成行的每个组合。该WHERE子句然后过滤对的a一边，然后是b边。结果是只返回满足两个条件的行，就像两个查询的交集一样。
回答by Nikhil Pareek
Break your problem in 2 statements: firstly, you want to select all if
用 2 条语句来解决你的问题：首先，如果你想全选
(id=3 and cut_name= '全プロセス' and cut_name='恐慌')
is true . Secondly, you want to select all if
是真的 。其次，如果你想全选
(id=3) and ( cut_name='全プロセス' or cut_name='恐慌')
is true. So, we will join both by OR because we want to select all if anyone of them is true.
是真的。因此，我们将通过 OR 加入两者，因为我们希望在其中任何一个为真时选择全部。
select * from emovis_reporting
where (id=3 and cut_name= '全プロセス' and cut_name='恐慌') OR
( (id=3) and ( cut_name='全プロセス' or cut_name='恐慌') )
回答by mclarenwcm
SELECT
campo1,
campo2,
campo3,
campo4
FROM tabela1
WHERE CONCAT(campo1,campo2,campo3,IF(campo4 IS NULL,'',campo4))
NOT IN
(SELECT CONCAT(campo1,campo2,campo3,IF(campo4 IS NULL,'',campo4))
FROM tabela2);
回答by deklin
AFAIR, MySQL implements INTERSECT through INNER JOIN.
AFAIR，MySQL 通过INNER JOIN实现 INTERSECT 。
