MySQL 中替代 Intersect 的方法

Question

我需要在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='恐慌') )

我知道MySQL中没有intersect操作符，因此我需要另一种方法。请指导我。

- Ankur Jariwala

---

对于任何给定的行，cut_name 不能有两个不同的值，因此第一个 "select" 不会返回任何内容。 - Marcelo Cantos

Answer 1

Microsoft SQL Server的 INTERSECT "返回同时出现在左侧和右侧查询结果中的任何不同值"，这与标准的INNER JOIN或WHERE 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

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     |
+-------+

对于这个特定的问题，涉及到id列，因此不会返回重复值，但为了完整起见，这里提供一个使用INNER JOIN和DISTINCT的MySQL替代方法：

SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);

+-------+
| value |
+-------+
| B     |
+-------+

以下是使用 WHERE ... IN 和 DISTINCT 的另一个示例：

SELECT DISTINCT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);

+-------+
| value |
+-------+
| B     |
+-------+

- Mike

---

为什么在 value 周围要加上括号？ - matanster

Answer 2

使用 UNION ALL 和 GROUP BY 可以更有效地生成交集。在处理大型数据集时，根据我的测试结果，性能可以提高两倍。

示例：

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;

这种UNION ALL-GROUP BY的解决方案更有效，因为MySQL会先查询第一个查询的结果，然后对于每一行，再查找第二个查询的结果。而使用INNER JOIN的解决方案，它会查找第一个查询的结果，然后对于每一行，都会查找第二个查询的结果。但是使用UNION ALL-GROUP BY的解决方案，它将查询第一个查询的结果和第二个查询的结果，然后一次性将所有结果进行分组。

- FBB

---

UNION ALL不会去重，我认为在这种情况下使用UNION更好，以获得准确的交集。 - code2be

这就是重点，你不想删除重复项，目的是为了能够计算结果的数量，并满足“HAVING count(*) >= 2”条件。如果不使用“UNION ALL”，这将无法实现。 - FBB

MySQL实际上没有任何自己的查询优化，可以选择更好的执行路径来处理这些可比较的查询吗？ - matanster

@Barmar：不会的，因为我们在每个子查询中使用了“DISTINCT”子句。 - FBB

@Barmar Ha，我的错！ - FBB

Answer 3

您的查询总是返回一个空记录集，因为 cut_name= '全プロセス' and cut_name='恐慌' 永远不会评估为 true。

通常，在MySQL中模拟 INTERSECT 应该像这样：

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

如果你的两张表都有被标记为NOT NULL的列，那么你可以省略IS NULL部分，并使用稍微更高效的IN重新编写查询：

SELECT  *
FROM    mytable m
WHERE   (col1, col2, col3) IN
        (
        SELECT  col1, col2, col3
        FROM    othertable o
        )

- Quassnoi

Answer 4

我刚在MySQL 5.7中检查了一下，很惊讶没有人提供一个简单的答案：使用NATURAL JOIN来查找交集，当表或（选择结果）具有相同的列时：

例如：

table1:

id，name，jobid

'1'，'John'，'1'

'2'，'Jack'，'3'

'3'，'Adam'，'2'

'4'，'Bill'，'6'

table2:

id，name，jobid

'1'，'John'，'1'

'2'，'Jack'，'3'

'3'，'Adam'，'2'

'4'，'Bill'，'5'

'5'，'Max'，'6'

以下是查询：

SELECT * FROM table1 NATURAL JOIN table2;

查询结果： id，姓名，工作ID

'1'，'约翰'，'1'

'2'，'杰克'，'3'

'3'，'亚当'，'2'

- Payam

---

这个答案是不正确的。NATURAL JOIN 不执行交集操作。根据 MySQL 文档：两个表的 NATURAL [LEFT] JOIN 被定义为在使用命名了两个表中存在的所有列的 USING 子句的情况下，语义上等同于 INNER JOIN 或 LEFT JOIN。 - FBB

Answer 5

从MySQL 8.0.31开始，INTERSECT得到本地支持。 INTERSECT Clause：

SELECT ... INTERSECT [ALL | DISTINCT] SELECT ... [INTERSECT [ALL | DISTINCT] SELECT ...]

INTERSECT限制了来自多个SELECT语句的结果，只保留所有语句都具有的行。

示例：

 SELECT 1 AS col
 INTERSECT 
 SELECT 1 AS col;

 -- output
 1

- Lukasz Szozda

Answer 6

为了完整起见，这里介绍另一种模拟INTERSECT的方法。请注意，在其他答案中建议使用的IN（SELECT ...）形式通常更有效。

通常，对于名为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")

（请注意，如果您在此查询中使用SELECT *，则会获得比mytable中定义的列多两倍的列，这是因为INNER JOIN生成笛卡尔积）

INNER JOIN在此处生成表中每一行对的排列组合。这意味着生成了每个可能的行组合，以每种可能的顺序。然后WHERE子句过滤a侧的对，然后是b侧。结果是仅返回同时满足两个条件的行，就像交集两个查询所做的那样。

- RobM

Answer 7

将问题分为两个语句：首先，您想要选择所有内容

(id=3 and cut_name= '全プロセス' and cut_name='恐慌')

是真的。其次，如果您想选择所有内容

(id=3) and ( cut_name='全プロセス' or cut_name='恐慌')

是真的。因此，我们将它们都连接起来使用OR，因为我们希望选择所有的内容，如果其中任何一个是真的。

select * from emovis_reporting
    where (id=3 and cut_name= '全プロセス' and cut_name='恐慌') OR
        ( (id=3) and ( cut_name='全プロセス' or cut_name='恐慌') )

- Nikhil Pareek

Answer 8

通过 INNER JOIN 或 IN() 模拟 INTERSECT 只能处理少量的 2-4 个连接集。如果您想要查找多个集合之间的交互作用，则需要使用多个 INNER JOIN。但是，在 MySQL 中使用多个 INNER JOIN 绝对不是稳定的解决方案，这可能导致查询计划执行器的纠缠，而在我们的情况下，查询会因为 100% 的 CPU 占用率而挂起数天。

您需要将 MySQL 更新到版本 8.0.31。它包含 INTERSECT 运算符。

- Anton Komarov

Answer 9

我使用IN来做交集操作

这是一个使用示例：

SELECT * FROM `test_oc_product` 
WHERE product_id IN ( SELECT product_id FROM test_oc_product_option WHERE option_id = '21' AND value = 'Red'  )
AND product_id IN ( SELECT product_id FROM test_oc_product_attribute WHERE attribute_id = '10' )

这里是转储

CREATE TABLE `test_oc_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `sku` varchar(64) NOT NULL,
   PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_oc_product` (`product_id`, `model`, `sku`) VALUES
(1, 'A1', 'A1'),
(2, 'A2', 'A2'),
(3, 'A3', 'A3'),
(4, 'AA', 'AA'),
(5, 'A5', 'A5'),
(6, 'A6', 'A6'),
(7, 'A7', 'A7'),
(8, 'A8', 'A8');

CREATE TABLE `test_oc_product_attribute` (
  `product_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`product_id`, `attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_oc_product_attribute` (`product_id`, `attribute_id`, `text`) VALUES 
('1', '10', 'Attribute Value 1'), 
('2', '11', 'Attribute Value 2');


CREATE TABLE `test_oc_product_option` (
  `product_option_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `value` text NOT NULL,
   PRIMARY KEY (`product_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test_oc_product_option` (`product_option_id`, `product_id`, `option_id`, `value`) VALUES 
(NULL, '1', '21', 'Red'), 
(NULL, '2', '21', 'Red'),
(NULL, '3', '21', 'Blue'),
(NULL, '4', '10', 'S');

- SergeTkach

Answer 10

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

- mclarenwcm

---

无论这是否正确，您都应该在代码块中格式化您的代码，并描述如何/为什么这解决了问题。 - Halvor Holsten Strand