作者:手机用户2602890485 | 来源:互联网 | 2022-12-02 16:11
我将此案例作为更大的SQL语句的一部分:
.
.
.
CASE
WHEN [AEC].item = 'S' THEN [sOld].code
WHEN [AEC].item= 'R' THEN [rOld].code
END AS [oldCode],
CASE
WHEN [AEC].item= 'S' THEN [sOld].system
WHEN [AEC].item= 'R' THEN [rOld].system
END AS [oldStatusSystem],
CASE
WHEN [AEC].item= 'S' THEN [sNew].code
WHEN [AEC].item= 'R' THEN [rNew].code
END AS [newCode],
CASE
WHEN [AEC].item= 'S' THEN [sNew].system
WHEN [AEC].item= 'R' THEN [rNew].system
END AS [newStatusSystem],
.
.
LEFT JOIN
tblSystemStatuses AS [sOld] ON [AEC].oldValue = [sOld].status
LEFT JOIN
tblSystemStatuses AS [sNew] ON [AEC].newValue = [sNew].status
LEFT JOIN
tblSystemRoles AS [rOld] ON [AEC].oldValue = [rOld].role
LEFT JOIN
tblSystemRoles AS [rNew] ON [AEC].newValue = [rNew].role
我想要的情况是这样的:
CASE
WHEN [AEC].item = 'S'
THEN
[sOld].code AS [oldCode],
[sOld].system AS [oldStatusSystem],
[sNew].code AS [newCode],
[sNew].system AS [newStatusSystem]
ELSE WHEN [AEC].item = 'R'
THEN
[rOld].code AS [oldCode],
[rOld].system AS [oldStatusSystem],
[rNew].code AS [newCode],
[rNew].system AS [newStatusSystem]
END
换句话说,我想作为then语句的一部分执行一些操作.我无法在任何地方看到一个例子.
我不能写if else因为我需要进行一些连接并依赖来自几个表的数据.有没有办法以我想要的方式写这个案子?
1> Gordon Linof..:
甲case
表达式仅返回单个值.所以,你不能做你想做的事.
您可以简化逻辑,但方式略有不同.可能最好的方法是使用cross apply
.请注意,这在from
子句中,因为select
can中的表达式只返回标量值.因此,您需要在选择中选择所需的列:
select v.oldcode, . . .
from . . . cross apply
(select v.*
from (values ('S', [sOld].code, [sOld].system, [sNew].code, [sNew].system),
('R', [rOld].code, [rOld].system, [rNew].code, [rNew].system)
) v(item, oldcode, oldsystem, newcode, newsystem)
where v.item = [AEC].item
) v
这满足了与单个case
表达式相同的需求.值得注意的是,匹配逻辑只出现一次.这个版本的优点是各个字段可以在多个表达式中使用select
.