SQL-如果column为null，则从另一个表获取值 [英] SQL - get value from another table if column is null

问题描述

我正在为数据协调系统建立匹配规则，并且需要您的建议来调整我的sql，因为它目前无法返回我所需的信息.
有2个源表:

I'm building matching rules for data reconciliation systems and need your advise on adjusting my sql for it as it currently doesn't return what I need.
There are 2 source tables:

      Table X                 Table Y
---------------------         ----------------------
Exec_ID   From  To            Exec_ID     From  To
1         A     B             1           B     C
2         A     B             2           B     C
3         A     B             3           B     C
4         A     B
                              5           B     C

匹配条件为:

X.To = Y.From
X.Exec_ID = Y.Exec_ID

如果存在A-> B，然后是B-> C，则最后应返回A->C.
如果只有A-> B，再没有B-> C，则应返回A-> B.

if there is A -> B and then B -> C, it should return A -> C in the end.
if there is only A -> B and no further B -> C, it should return A -> B.

因此，输出应为以下内容.

So the output should be the following.

From    To
---------
A       C
A       C
A       C
A       B

我正在使用的SQL是:

SQL I'm using is:

select X.From, Y.To
from x
    left outer join y on
    x.To = Y.From
    and x.Exec_ID = y.Exec_ID

它返回类似的值

A C
A C
A C
A Null

因此最后一条记录不正确，因为它应该是AB.请帮助进行调整.

So the last record is incorrect as it should be A B. Please help to adjust.

推荐答案

检查是否为空?

select X.From, [To] = COALESCE(Y.To, X.To)
from x
    left outer join y on
    x.To = Y.From
    and x.Exec_ID = y.Exec_ID

这篇关于SQL-如果column为null，则从另一个表获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！