TA貢獻1852條經(jīng)驗 獲得超7個贊
Microsoft SQL Server的INTERSECT “返回由IntersectOperand左側和右側的查詢返回的任何不同值”這與標準不同INNER JOIN或WHERE EXISTS查詢。
SQLServer
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列，所以不會返回重復的值，但是為了完整起見，這里有一個MySQL替代方法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 |
+-------+
TA貢獻1815條經(jīng)驗 獲得超13個贊
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;
TA貢獻1829條經(jīng)驗 獲得超4個贊
cut_name= '全プロセス' and cut_name='恐慌'true.
INTERSECTMySQL
SELECT *FROM mytable mWHERE 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 NULLIS NULLIN:
SELECT *FROM mytable mWHERE (col1, col2, col3) IN ( SELECT col1, col2, col3 FROM othertable o )
舉報