三张桌子上的全部外部联接 [英] Full Outer Join on Three Tables

问题描述

一个人如何在三个表上进行完全外部联接?

How would one carry out a full outer join on three tables?

我已经在两个表上成功进行了联接，但是对于如何将第三个表添加到查询中，我一无所知.

I have already carried my join successfully on two tables, but I am clueless on how to add the third table to my query.

这是我(漫长的)查询:

Here is my (long-winded) query:

SELECT MONTH(t1.datetime) AS month, COUNT(DISTINCT t1.column) AS t1Count, COUNT(DISTINCT t2.column) AS t2Count
FROM t1
LEFT OUTER JOIN t2 ON MONTH(t2.datetime) = MONTH(t1.datetime)
GROUP BY MONTH(t1.datetime)
UNION
SELECT MONTH(t1.datetime) AS month, COUNT(DISTINCT t1.column) AS t1Count, COUNT(DISTINCT t2.column) AS t2Count
FROM t1 
RIGHT OUTER JOIN t2 ON MONTH(t2.datetime) = MONTH(t1.datetime)
GROUP BY MONTH(t2.datetime)

非常感谢任何指导.

推荐答案

尝试此查询-

(已编辑)

SELECT
  t.month,
  COUNT(DISTINCT t1.column) t1count,
  COUNT(DISTINCT t2.column) t2count,
  COUNT(DISTINCT t3.column) t3count
FROM 
  (SELECT MONTH(datetime) AS month FROM t1
    UNION
  SELECT MONTH(datetime) AS month FROM t2
    UNION
  SELECT MONTH(datetime) AS month FROM t3
  ) t
  LEFT JOIN t1
    ON t.month = MONTH(t1.datetime)
  LEFT JOIN t2
    ON t.month = MONTH(t2.datetime)
  LEFT JOIN t3
    ON t.month = MONTH(t3.datetime)
  GROUP BY
    month

这篇关于三张桌子上的全部外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！