三个表上的完全外连接

发布于 2025-01-04 06:49:21 字数 559 浏览 2 评论 0原文

如何对三个表执行完全外部联接？

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

这是我的（冗长的）询问：

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)

非常感谢任何指导。

原文

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)

Any guidance is much appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

洛阳烟雨空心柳 2025-01-11 06:49:21

尝试此查询 -

（已编辑）

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

Try this query -

(edited)

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

回复收藏 0 原文

~没有更多了~