在MySQL中相交的替代方法 [英] Alternative to Intersect in MySQL
问题描述
我需要在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
或
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
的MySQL替代方法:
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
的示例:
SELECT DISTINCT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);
+-------+
| value |
+-------+
| B |
+-------+
这篇关于在MySQL中相交的替代方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!