SQL Full外部联接或替代解决方案 [英] SQL Full outer join or alternative solution

问题描述

我正在尝试使用full outer join将多个表连接在一起，它接近正确的结果，但是由于join子句的缘故，存在一些重复的行.我有几个带有ID，日期，值列的表.我正在寻找一张表格，其中每个ID，日期对都具有一行，其中包含每个表格的所有值.

I'm trying to join multiple tables together using a full outer join, its getting close to the proper result but there are some duplicate rows due to the join clauses. I've got several tables with columns of id, date, value. I'm looking to get a table with one row for each id, date pair that has all the values from each of the tables.

如果您想使用它，这里是一个SQLFiddle.

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

Here's what I've got so far:

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的ID，event_dt对不在T1中时，我得到了一些重复的行(可以预料，因为那就是我要加入的内容).例如，我会得到类似的东西:

This almost works, but I get some duplicate rows when for instance T4 has an ID,event_dt pair that is not in T1 (as to be expected because thats what I'm joining on). For instance, I'll get something like:

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)

当我想要获得:

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

是否有一种方法可以将这些行放平/合并在一起，或者是否有更好的方法可以完全解决这些问题?

Is there a way to flatten/merge those rows together, or is there a better way to go about this altogether?

推荐答案

(假设OP要求完全对称的外部4联接)

(assuming the OP wants a fully symmetric outer 4-join)

WITH four AS (
        SELECT id, event_dt FROM t1
        UNION
        SELECT id, event_dt FROM t2
        UNION
        SELECT id, event_dt FROM t3
        UNION
        SELECT id, event_dt FROM t4
        )
SELECT f.id, f.event_dt
        , t1.amt1
        , t2.amt2
        , t3.amt3
        , t4.amt4
FROM four f
LEFT JOIN t1 ON t1.id = f.id AND t1.event_dt = f.event_dt
LEFT JOIN t2 ON t2.id = f.id AND t2.event_dt = f.event_dt
LEFT JOIN t3 ON t3.id = f.id AND t3.event_dt = f.event_dt
LEFT JOIN t4 ON t4.id = f.id AND t4.event_dt = f.event_dt
ORDER BY id, event_dt
        ;

结果:

 id |  event_dt  | amt1 | amt2 | amt3 | amt4 
----+------------+------+------+------+------
  1 | 2012-04-01 |    1 |      |      |     
  1 | 2012-04-02 |    1 |      |    3 |     
  1 | 2012-04-03 |    1 |      |    3 |     
  1 | 2012-04-06 |      |    2 |    3 |    4
  1 | 2012-04-07 |      |    2 |      |     
  2 | 2012-04-01 |   40 |      |      |     
  2 | 2012-04-02 |      |      |    3 |     
  2 | 2012-04-03 |      |      |    3 |     
  2 | 2012-04-04 |   40 |      |      |     
(9 rows)

顺便说一句:在UNION四之后，LEFT JOIN将与FULL JOIN相同(第四联盟已经具有所有可能的{id，event_dt}对)

BTW: after the UNION four, LEFT JOINs will do the same as FULL JOINs here (union four already has all the possible {id, event_dt} pairs)

这篇关于SQL Full外部联接或替代解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！