SQL Full outer join 或替代解决方案

Question

我正在尝试使用 full outer join 将多个表连接在一起，它接近正确的结果，但由于连接子句，有一些重复的行。我有几个包含 id、date、value 列的表。我正在寻找一个表，其中每个 ID、日期对都有一行，其中包含每个表中的所有值。

Here is a SQLFiddle if you want to play with it.

到目前为止，这是我得到的:

SELECT
  COALESCE(T1.ID, T2.ID, T3.ID, t4.id) AS ID,
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt) AS DATE,
  T1.AMT1, T2.AMT2, T3.AMT3, t4.AMT4
FROM T1
FULL OUTER JOIN T2
ON
  T2.id = T1.id
  AND T2.event_dt = T1.event_dt
FULL OUTER JOIN T3
ON
  T3.id = T1.id
  AND T3.event_dt = T1.event_dt
FULL OUTER JOIN T4
ON
  T4.id = T1.id
  AND T4.event_dt = T1.event_dt
ORDER BY ID, DATE

这几乎可以工作，但是当例如 T4 有一个不在 T1 中的 ID,event_dt 对时，我会得到一些重复的行(正如预期的那样，因为这就是我加入的内容)。例如，我会得到如下内容:

1   April, 06 2012 00:00:00+0000    (null)  2   (null)  (null)
1   April, 06 2012 00:00:00+0000    (null)  (null)  (null)  4
1   April, 06 2012 00:00:00+0000    (null)  (null)  3   (null)



<p>When I'm looking to get:
</p><pre>1 April, 06 2012 00:00:00+0000 (null) 2 3 4
</pre>

是否有办法将这些行拼合/合并在一起，或者是否有更好的方法来解决这个问题？

Answer 1

我认为您 join-citeria 根本不是您真正想要的。这个应该可以解决问题:

SELECT
  COALESCE(T1.ID, T2.ID, T3.ID, t4.id) AS ID,
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt) AS DATE,
  T1.AMT1, T2.AMT2, T3.AMT3, t4.AMT4
FROM T1
FULL OUTER JOIN T2
ON
  T2.id = T1.id
  AND T2.event_dt = T1.event_dt
FULL OUTER JOIN T3
ON
  T3.id = coalesce(T1.id, T2.id)
  AND T3.event_dt = coalesce(T1.event_dt, T2.event_dt)
FULL OUTER JOIN T4
ON
  T4.id = coalesce(T1.id, T2.id, T3.id)
  AND T4.event_dt = coalesce(T1.event_dt, T2.event_dt, T3.event_dt)
ORDER BY ID, DATE

SQL-Fiddle here为您提供 2012-04-06 所需的输出。