Oracle 10 SQL: FULL JOIN 通过交叉引用表

Question

http://sqlfiddle.com/#!4/24637/1

我有三个表(sqlfiddle 链接中显示的更好的详细信息/数据(，一个替换另一个，中间有一个交叉引用表。每个表中的一个字段使用交叉引用(版本(，每个表中的另一个字段相同 (changeID(。

我需要一个查询，当传递一个 new_version + new_changeType 的列表时，以及等效的 original_version + old_changeType(如果有旧版本的等效版本(加上任何在数据转换中"错过"的旧更改 ID。

                        TABLES (fields on the same line are equivalent)
OLD_table         |   XREF_table         |  NEW_Table
original_version  |   original_version   |
changeID          |                      |  changeID
OLD_changeType    |                      |
                  |   new_version        |  new_version  
                  |                      |  NEW_changeType

                         DATA
111,1,CT1       |     111,AAA            |  AAA,1,ONE
111,2,CT2       |     222,BBB            |  AAA,2,TWO
222,1,CT1       |     333,DDD            |  BBB,1,ONE
222,2,CT2       |                        |  BBB,2,TWO
222,3,CT3       |                        |  CCC,1,ONE
333,1,CT1       |                        |
444,1,CT1       |                        |

如果传递了以下列表，则结果集应如下所示。(顺序无关紧要(

 AAA,BBB,CCC
 | NEW_VERSION | NEW_CHANGE_TYPE| ORIGINAL_VERSION | CHANGEID | OLD_CHANGE_TYPE |
 |-------------|----------------|------------------|----------|-----------------|
 |         AAA |            ONE |              111 |        1 |             CT1 |
 |         AAA |            TWO |              111 |        2 |             CT2 |
 |         BBB |            ONE |              222 |        1 |             CT1 |
 |         BBB |            TWO |              222 |        2 |             CT2 |
 |         CCC |            ONE |           (null) |   (null) |          (null) |
 |      (null) |         (null) |              222 |        3 |             CT3 |

我在获取所需的所有数据时遇到问题。我已经使用了以下查询，但是我似乎 1( 错过了一行或 2( 获取不符合要求的其他行。

我玩过的以下查询如下。

select 
a.new_version, 
a.Change_type, 
c.original_version,
c.changeID,
c.OLD_Change_type
from NEW_TABLE a
LEFT OUTER JOIN XREF_TABLE b on a.new_version = b.new_version
FULL OUTER JOIN OLD_TABLE c on 
 b.original_version = c.original_version and a.changeID = c.changeID
where (b.new_version in ('AAA','BBB','CCC') or b.new_version is null);
select 
a.new_version, 
a.Change_type, 
c.original_version,
c.changeID,
c.OLD_Change_type
from NEW_TABLE a
 FULL JOIN XREF_TABLE b on a.new_version = b.new_version
 FULL JOIN OLD_TABLE c on 
 b.original_version = c.original_version and a.changeID = c.changeID
where (a.new_version in ('AAA','BBB','CCC'));

第一个返回一个包含 333，DDD 数据的"额外"行，该行未从输入中指定。秒返回少一行(旧表中的更改 ID 从转换此数据时"丢失"。

关于如何解决这个问题的任何想法或建议？

Answer 1

第一个内部联接old_table和xref_table，因为您对任何没有xref_table条目的old_table条目都不感兴趣。然后完全外联接new_table。在 WHERE 子句中，请注意 new_table.new_version 可以为空，因此在这种情况下使用 mergeesce 使用 xref_table.new_version 将结果限制为 AAA、BBB 和 CCC。就这样。

select 
  coalesce(n.new_version, x.new_version) as new_version,
  n.change_type, 
  o.original_version,
  o.changeid,
  o.old_change_type
from old_table o 
inner join xref_table x 
  on x.original_version = o.original_version
full outer join new_table n 
  on n.new_version = x.new_version 
  and n.changeid = o.changeid
where coalesce(n.new_version, x.new_version) in ('AAA','BBB','CCC')
order by 1,2,3,4,5
;

这是你的小提琴：http://sqlfiddle.com/#!4/24637/11。

顺便说一句：最好不要使用随机别名，如 a、b 和 c，它们不会指示表的含义。这使得查询更难理解。请改用表的第一个字母或首字母缩略词。