MySQL 中相交的替代方案

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 。所以我需要另一种方式。 请指导我。

Answer 1

Microsoft SQL Server 的 INTERSECT "返回由 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：

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

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;

它更有效，因为使用INNER JOIN解决方案，MySQL将查找第一个查询的结果，然后对于每一行，查找第二个查询的结果。使用 UNION ALL-GROUP BY 解决方案，它将查询第一个查询的结果、第二个查询的结果，然后将结果一次性分组在一起。

Answer 3

您的查询将始终返回空记录集，因为 cut_name= '全プロセsu' 和 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
        )

Answer 4

我刚刚在 MySQL 5.7 中检查过它，非常惊讶没有人提供一个简单的答案： NATURAL JOIN

当表或（选择结果）具有相同的列时，您可以使用 NATURAL JOIN 作为查找交集的方法：

< /a>

例如：

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、姓名、jobid

'1'、'约翰'、'1'

'2'、'杰克'、'3'

'3'、'亚当'、'2'

Answer 5

为了完整起见，这里有另一种模拟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 端。结果是仅返回满足两个条件的行，就像交集两个查询所做的那样。

Answer 6

从 MySQL 8.0.31 开始，原生支持 INTERSECT。

INTERSECT 子句：

选择...
相交[全部|不同]选择...
[相交[全部|不同]选择...]

INTERSECT 将多个 SELECT 语句的结果限制为所有公共行。

样本：

 SELECT 1 AS col
 INTERSECT 
 SELECT 1 AS col;

 -- output
 1

Answer 7

我使用 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');

Answer 8

将您的问题分为两个语句：首先，您要选择所有 if

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

is true 。其次，您要选择所有 if

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

为 true 的情况。因此，我们将通过 OR 连接两者，因为如果其中任何一个为真，我们想要选择全部。

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

Answer 9

通过 INNER JOIN 或 IN() 模拟 INTERSECT 只能用于少数 2-4 个连接集。如果您尝试查找多个集合之间的交互，则需要使用多个 INNER JOIN。但是 MySQL 中的多个 INNER JOIN 绝对是不稳定的解决方案，这可能会导致查询计划执行器的纠缠，在我们的例子中，查询计划执行器会挂起几天，CPU 消耗 100%。

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

Answer 10

AFAIR，MySQL 通过 INNER JOIN 实现 INTERSECT。

Answer 11

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