我有 2 张表 - 客户和例外。例外应用于客户表中的一个或多个列。
我正在尝试根据列在另一个表中的存在来重命名列。
例如:如果例外是重命名客户,我需要在最终输出中显示 John Smith 代替 John。其他名称应保持不变。
有没有更好的方法可以在不使用更新语句但在同一查询中实现此目的?
select id,
(select case when b.exception = 'Rename Customers' and b.customername =
a.customername then b.newname else a.customername end from exception
b where b.activeInd = 'Y') as customername
,sum(wt) as wt
from customers
group by id
客户
ID customername address wt
1 John 123 via court 4.12
2 Sarah zzyx road 3.18
3 Jill one air force drive 2.32
4 Julian 2218 via las pas 1.18
5 Karto KR38 Mount saint 1.32
异常
exceptionID exception customername address oldname newname activeInd
1 Rename Customers John John John Smith Y
2 Rename address 2218 via pas 2218 via pas 2218 via
pas, NY N
输出
ID customername wt
1 null 4.12
2 null 3.18
3 null 2.32
4 null 1.18
5 null 1.32
预期成果
ID customername wt
1 John Smith 4.12
2 Sarah 3.18
3 Jill 2.32
如果我理解正确,你想要LEFT JOIN
s:
SELECT c.id,
COALESCE(en.newname, c.customername) as customername,
c.wt
FROM customers c LEFT JOIN
exceptions en
ON en.exception = 'Rename Customers' AND
en.oldname = c.customername AND
en.activeInd = 'Y';
对于其他字段,您可以使用多个JOIN
进行扩展:
SELECT c.id,
COALESCE(en.newname, c.customername) as customername,
COALESCE(ea.newname, c.address) as address,
c.wt
FROM customers c LEFT JOIN
exceptions en
ON en.exception = 'Rename Customers' AND
en.oldname = c.customername AND
en.activeInd = 'Y' LEFT JOIN
exceptions ea
ON ea.exception = 'Rename Address' AND
ea.oldname = c.address AND
ea.activeInd = 'Y';
您可以使用具有相关条件的exceptions
表LEFT JOIN
customers
表。然后你可以用COALESCE
来实现替换逻辑:当找到匹配的记录时,你想显示newname
,否则坚持当前customername
。
SELECT
c.id,
COALESCE(e.newname, c.customername) customername,
c.wt
FROM customers c
LEFT JOIN exceptions e
ON e.exception = 'Rename'
AND e.customername = c.customername
AND e.activeInd = 'Y'