SQL按日期合并3个表，其中某些日期丢失 [英] SQL Merge 3 tables by date where some dates are missing

问题描述

我有 3 个要合并的表，每个表都有不同的感兴趣的列.我还有一个 id 变量，我想在内部"id 中进行单独的合并.这个想法是我想按日期(在 ID 内)合并 X、Y 和 Z，如果特定变量不存在该日期，则缺少值.

I have 3 tables that I want to merge, each with a different column of interest. I also have an id variable that I want to do separate merges "within" id. The idea is that I want to merge X, Y, and Z by date (within ID), and have missing values if that date does not exist for a particular variable.

Table X:
ID     Date         X
1      2012-01-01   101
1      2012-01-02   102
1      2012-01-03   103
1      2012-01-04   104
1      2012-01-05   105
2      2012-01-01   150

Table Y:
ID     Date         Y
1      2012-01-01   301
1      2012-01-02   302
1      2012-01-03   303
1      2012-01-11   311
2      2012-01-01   350

Table Z:
ID     Date         Z
1      2012-01-01   401
1      2012-01-03   403
1      2012-01-04   404
1      2012-01-11   411
1      2012-01-21   421
2      2012-01-01   450

Desired Result Table:
ID     Date         X     Y     Z
1      2012-01-01   101   301   401
1      2012-01-02   102   302   .
1      2012-01-03   103   303   403
1      2012-01-04   104   .     404
1      2012-01-05   105   .     .
1      2012-01-11   .     311   411
1      2012-01-21   .     .     421
2      2012-01-01   150   350   450

任何想法如何编写此 SQL 语句?我试过搞乱完全连接"和交叉产品的 where 语句，但我不断收到一些 ID-日期组合的重复值，或者有时没有 ID.

Any ideas how to write this SQL statement? I've tried messing around with "full joins" and where statements for cross products, but I keep getting duplicate values for some of my ID-date combinations, or sometimes no ID.

任何帮助将不胜感激.

推荐答案

联接可能是一件棘手的事情.我通常的做法是先形成一组Keys，然后用这些keys得到我想要的.

Joins can be tricky things. My usual approach is to form the set of Keys first, and then use those keys to get what I want.

SELECT source.ID, source.Date, x.X, y.Y, z.Z
FROM
(
  SELECT ID, Date
  FROM TableX
  UNION
  SELECT ID, Date
  FROM TableY
  UNION
  SELECT ID, Date
  FROM TableZ
) as source
LEFT JOIN TableX x ON source.ID = x.ID AND source.Date = x.Date
LEFT JOIN TableY y ON source.ID = y.ID AND source.Date = y.Date
LEFT JOIN TableZ z ON source.ID = z.ID AND source.Date = z.Date
ORDER BY source.ID, source.Date

这篇关于SQL按日期合并3个表，其中某些日期丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！