英文:
Compare columns returned by two sub-queries for distinct values
问题
在MySQL Workbench 8.0中，您可以尝试以下解决方案：
```sql
SELECT number FROM table1 WHERE my_condition AND number NOT IN (SELECT number FROM table2 WHERE my_condition)
UNION
SELECT number FROM table2 WHERE my_condition AND number NOT IN (SELECT number FROM table1 WHERE my_condition);
<details>
<summary>英文:</summary>
I have two queries say, A and B.
- A = `SELECT number FROM table1 WHERE my_condition;`
- B = `SELECT number FROM table2 WHERE my_condition;`
Both of these result in a column which has numbers e.g.
- A returns a single column: `[1, 8, 3, 4, 5]`
- B returns a single column: `[1, 7, 3, 4, 6, 2, 5]`
I want to know which numbers don't exist in each other's table. So, result will be: `[2, 6, 7, 8]`.
What I am trying is the following in MySQL Workbench 8.0:
DROP TEMPORARY TABLE IF EXISTS temp_table1;
DROP TEMPORARY TABLE IF EXISTS temp_table2;
CREATE TEMPORARY TABLE temp_table1
SELECT number FROM table1 WHERE my_condition; /* This is my query 'A' */
CREATE TEMPORARY TABLE temp_table2
SELECT number FROM table2 WHERE my_condition; /* This is my query 'B' */
SELECT number FROM temp_table1 WHERE number NOT IN (SELECT number FROM temp_table2)
UNION
SELECT number FROM temp_table2 WHERE number NOT IN (SELECT number FROM temp_table1);
DROP TEMPORARY TABLE temp_table1;
DROP TEMPORARY TABLE temp_table2;
This doesn't work and I get an error that "cannot reopen temporary table". I have no idea about this. Any similar possibly easier to understand solution that does work in MySQL?
</details>
# 答案1
**得分**: 1
以下是您要翻译的内容：
您可以使用以下的联合查询：
```sql
SELECT t1.number
FROM table1 t1
LEFT JOIN table2 t2 ON t2.number = t1.number
WHERE <condition> AND t2.number IS NULL
UNION ALL
SELECT t2.number
FROM table1 t1
RIGHT JOIN table2 t2 ON t2.number = t1.number
WHERE <condition> AND t1.number IS NULL
查询的第一部分找到了第一张表中唯一的数字，而第二部分找到了第二张表中唯一的数字。
如果您希望使用您当前的方法并且使用通用表达式 (Common Table Expressions，适用于 MySQL 8+)，可以尝试以下方式：
WITH temp_table1 AS (
SELECT number FROM table1 WHERE my_condition
),
temp_table2 AS (
SELECT number FROM table2 WHERE my_condition
)
SELECT number FROM temp_table1 WHERE number NOT IN (SELECT number FROM temp_table2)
UNION
SELECT number FROM temp_table2 WHERE number NOT IN (SELECT number FROM temp_table1);
注意：在上述 SQL 查询中，
英文:
You could use the following union query:
<!-- language: sql -->
SELECT t1.number
FROM table1 t1
LEFT JOIN table2 t2 ON t2.number = t1.number
WHERE <condition> AND t2.number IS NULL
UNION ALL
SELECT t2.number
FROM table1 t1
RIGHT JOIN table2 t2 ON t2.number = t1.number
WHERE <condition> AND t1.number IS NULL
The first half of the query finds numbers unique to the first table, while the second half finds numbers unique to the second table.
Using your exact current approach with common table expressions (available from MySQL 8+), we can try:
<!-- language: sql -->
WITH temp_table1 AS (
SELECT number FROM table1 WHERE my_condition
),
temp_table2 AS (
SELECT number FROM table2 WHERE my_condition
)
SELECT number FROM temp_table1 WHERE number NOT IN (SELECT number FROM temp_table2)
UNION
SELECT number FROM temp_table2 WHERE number NOT IN (SELECT number FROM temp_table1);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论