在 MySQL 中相交的替代方法

2021-11-20 00:00:00 mysql intersect

我需要在 MySQL 中实现以下查询.

I need to implement the following query in 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 中.所以我需要另一种方式.请指导我.

I know that intersect is not in MySQL. So I need another way. Please guide me.

推荐答案

Microsoft SQL Server 的 INTERSECT 返回查询在 INTERSECT 操作数的左侧和右侧返回的任何不同值" 这与标准的INNER JOIN 或 WHERE EXISTS 查询.

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 JOIN or WHERE EXISTS query.

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:

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 JOIN and DISTINCT:

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

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

另一个使用 WHERE ... IN 和 DISTINCT 的例子:

And another example using WHERE ... IN and DISTINCT:

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

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

相关文章