問題描述

我需要在 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:

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

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

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

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