我需要在MySQL上运行以下命令,但它不允许完全的外部联接:
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;
我如何在MySQL中做到这一点?
我知道如何对两个表这样做,例如,按照this example,我可以做:
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
不是:
SELECT * FROM t1 FULL OUTER JOIN t2
ON t1.id = t2.id
但是我该如何做我的第一个例子呢?我得出的以下结论似乎是错误的:
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
因为下面这一点似乎太严格了:
WHERE t0.date IS NULL
AND t2.date IS NULL
AND t3.date IS NULL