英文:
Alternative to doing FULL OUTER JOIN for multiple tables?
问题
I needed to run the following on MySQL, but it doesn't allow FULL OUTER JOINs:
SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2
COALESCE(t3.hits3, 0) AS hits3
FROM t0
FULL OUTER JOIN t1 ON t0.date = t1.date
FULL OUTER JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date;
FULL OUTER JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date;
How can I do that in MySQL?
I understand how to do it for two tables, for example, as per [this example][1], I could do:
```sql
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL
Instead of:
SELECT * FROM t1 FULL OUTER JOIN t2
ON t1.id = t2.id
But how do I do my first example? The following I've come up with seems wrong:
SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2,
COALESCE(t3.hits3, 0) AS hits3
FROM t0
LEFT JOIN t1 ON t0.date = t1.date
LEFT JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date
LEFT JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date
UNION ALL
SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2,
COALESCE(t3.hits3, 0) AS hits3
FROM t1
RIGHT JOIN t0 ON t0.date = t1.date
RIGHT JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date
RIGHT JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date
WHERE t0.date IS NULL
AND t2.date IS NULL
AND t3.date IS NULL
Because the following bit seems too restrictive:
WHERE t0.date IS NULL
AND t2.date IS NULL
AND t3.date IS NULL
<details>
<summary>英文:</summary>
I needed to run the following on MySQL, but it doesn't allow FULL OUTER JOINs:
SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2
COALESCE(t3.hits3, 0) AS hits3
FROM t0
FULL OUTER JOIN t1 ON t0.date = t1.date
FULL OUTER JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date;
FULL OUTER JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date;
How can I do that in MySQL?
I understand how to do it for two tables, for example, as per [this example][1], I could do:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL
Instead of:
SELECT * FROM t1 FULL OUTER JOIN t2
ON t1.id = t2.id
But how do I do my first example? The following I've come up with seems wrong:
SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2,
COALESCE(t3.hits3, 0) AS hits3
FROM t0
LEFT JOIN t1 ON t0.date = t1.date
LEFT JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date
LEFT JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date
UNION ALL
SELECT COALESCE(t0.date, t1.date, t2.date, t3.date) AS date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2,
COALESCE(t3.hits3, 0) AS hits3
FROM t1
RIGHT JOIN t0 ON t0.date = t1.date
RIGHT JOIN t2 ON COALESCE(t0.date, t1.date) = t2.date
RIGHT JOIN t3 ON COALESCE(t0.date, t1.date, t2.date) = t3.date
WHERE t0.date IS NULL
AND t2.date IS NULL
AND t3.date IS NULL
Because the following bit seems too restrictive:
WHERE t0.date IS NULL
AND t2.date IS NULL
AND t3.date IS NULL
[1]: https://stackoverflow.com/questions/4796872/how-can-i-do-a-full-outer-join-in-mysql
</details>
# 答案1
**得分**: 2
```plaintext
你可以通过应用联合方法使这段代码工作，但对于每个连续的交叉连接，使用单独的CTE：
<!-- language: sql -->
WITH cte1 AS (
选择 t0.date 作为 date0, t1.date 作为 date1, t0.hits1, t1.hits2
从 t0
左连接 t1 ON t1.date = t0.date
UNION ALL
选择 t0.date, t1.date, t0.hits1, t1.hits2
从 t0
右连接 t1 ON t1.date = t0.date
其中 t0.date 为 NULL
),
cte2 AS (
选择 t1.date0, t1.date1, t2.date 作为 date2,
t1.hits1, t1.hits2, t2.hits3
从 cte1 t1
左连接 t2 ON COALESCE(t1.date0, t1.date1) = t2.date
UNION ALL
选择 t1.date0, t1.date1, t2.date, t1.hits1, t1.hits2, t2.hits3
从 cte1 t1
右连接 t2 ON COALESCE(t1.date0, t1.date1) = t2.date
其中 COALESCE(t1.date0, t1.date1) 为 NULL
),
cte3 AS (
选择 t2.date0, t2.date1, t2.date2, t3.date 作为 date3,
t2.hits1, t2.hits2, t2.hits3, t3.hits4
从 cte2 t2
左连接 t3 ON COALESCE(t2.date0, t2.date1, t2.date2) = t3.date
UNION ALL
选择 t2.date0, t2.date1, t2.date2, t3.date,
t2.hits1, t2.hits2, t2.hits3, t3.hits4
从 cte2 t2
右连接 t3 ON COALESCE(t2.date0, t2.date1, t2.date2) = t3.date
其中 COALESCE(t2.date0, t2.date1, t2.date2) 为 NULL
)
选择 COALESCE(date0, date1, date2, date3) 作为 date,
COALESCE(hits1, 0) 作为 hits0,
COALESCE(hits2, 0) 作为 hits1,
COALESCE(hits3, 0) 作为 hits2
COALESCE(hits3, 0) 作为 hits3
从 cte3;
英文:
You can make this work by applying the union approach, but using separate CTEs for each sequential cross join:
<!-- language: sql -->
WITH cte1 AS (
SELECT t0.date AS date0, t1.date AS date1, t0.hits1, t1.hits2
FROM t0
LEFT JOIN t1 ON t1.date = t0.date
UNION ALL
SELECT t0.date, t1.date, t0.hits1, t1.hits2
FROM t0
RIGHT JOIN t1 ON t1.date = t0.date
WHERE t0.date IS NULL
),
cte2 AS (
SELECT t1.date0, t1.date1, t2.date AS date2,
t1.hits1, t1.hits2, t2.hits3
FROM cte1 t1
LEFT JOIN t2 ON COALESCE(t1.date0, t1.date1) = t2.date
UNION ALL
SELECT t1.date0, t1.date1, t2.date, t1.hits1, t1.hits2, t2.hits3
FROM cte1 t1
RIGHT JOIN t2 ON COALESCE(t1.date0, t1.date1) = t2.date
WHERE COALESCE(t1.date0, t1.date1) IS NULL
),
cte3 AS (
SELECT t2.date0, t2.date1, t2.date2, t3.date AS date3,
t2.hits1, t2.hits2, t2.hits3, t3.hits4
FROM cte2 t2
LEFT JOIN t3 ON COALESCE(t2.date0, t2.date1, t2.date2) = t3.date
UNION ALL
SELECT t2.date0, t2.date1, t2.date2, t3.date,
t2.hits1, t2.hits2, t2.hits3, t3.hits4
FROM cte2 t2
RIGHT JOIN t3 ON COALESCE(t2.date0, t2.date1, t2.date2) = t3.date
WHERE COALESCE(t2.date0, t2.date1, t2.date2) IS NULL
)
SELECT COALESCE(date0, date1, date2, date3) AS date,
COALESCE(hits1, 0) AS hits0,
COALESCE(hits2, 0) AS hits1,
COALESCE(hits3, 0) AS hits2
COALESCE(hits3, 0) AS hits3
FROM cte3;
答案2
得分: 1
以下是代码部分的翻译：
这可能是一个对于您的情况的可行替代方法，您可以首先构建所有日期的完整并左连接到每个表格中：
or possibly replacing the union of all the dates with a recursive cte:
and you could add a having clause to remove dates with no hits:
请注意，我已经为您翻译了代码部分，不包括代码块的内容。如果您需要更多的翻译，请告诉我。
英文:
This may, or may not, be a viable alternative approach for your situation. You could build a full union of all the dates first and then left join to each table:
SELECT
d.date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2
COALESCE(t3.hits3, 0) AS hits3
FROM (
SELECT date FROM t0 UNION
SELECT date FROM t1 UNION
SELECT date FROM t2 UNION
SELECT date FROM t3
) d
LEFT JOIN t0 ON d.date = t0.date
LEFT JOIN t1 ON d.date = t1.date
LEFT JOIN t2 ON d.date = t2.date
LEFT JOIN t3 ON d.date = t3.date;
or possibly replacing the union of all the dates with a recursive cte:
WITH RECURSIVE d (date) AS (
SELECT '2023-01-01'
UNION ALL
SELECT date + INTERVAL 1 DAY FROM d
WHERE date + INTERVAL 1 DAY <= CURRENT_DATE
)
SELECT d.date,
COALESCE(t0.hits1, 0) AS hits0,
COALESCE(t1.hits2, 0) AS hits1,
COALESCE(t2.hits3, 0) AS hits2
COALESCE(t3.hits3, 0) AS hits3
FROM d
LEFT JOIN t0 ON d.date = t0.date
LEFT JOIN t1 ON d.date = t1.date
LEFT JOIN t2 ON d.date = t2.date
LEFT JOIN t3 ON d.date = t3.date;
and you could add a having clause to remove dates with no hits:
HAVING hits0 OR hits1 OR hits2 OR hits3
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论