SQL - 在同一查询中填充多个变量

Question

我正在尝试在同一个查询中加载多个变量，如下所示

declare @currentUserPersonnelNumber int
declare @draftWorkFlowStatusId int 
declare @diWorkFlowStatusId int 
declare @ibWorkFlowStatusId int 
declare @ipWorkFlowStatusId int 

select 
    @draftWorkFlowStatusId = case when WFStep='DR' then WorkFlowId else NULL end,
    @diWorkFlowStatusId = case when WFStep='DI' then WorkFlowId else NULL end,
    @ibWorkFlowStatusId = case when WFStep='IB' then WorkFlowId else NULL end,
    @ipWorkFlowStatusId = case when WFStep='IP' then WorkFlowId else NULL end
 from WorkFlow

但只有第二个变量 @diWorkFlowStatusId 被填充，而不是全部。

我做错了什么？

当我这样做时，所有变量都会正确加载，但我认为这不是正确的方法

declare @draftWorkFlowStatusId int = (SELECT WorkFlowId FROM [WorkFlow] WHERE WFStep = 'DR')
declare @diWorkFlowStatusId int = (SELECT WorkFlowId FROM [WorkFlow] WHERE WFStep = 'DI') 
declare @ibWorkFlowStatusId int = (SELECT WorkFlowId FROM WorkFlow WHERE WFStep = 'IB') 
declare @ipWorkFlowStatusId int = (SELECT WorkFlowId FROM WorkFlow WHERE WFStep = 'IP') 

Answer 1

您必须使用聚合函数:

declare @currentUserPersonnelNumber int
declare @draftWorkFlowStatusId int 
declare @diWorkFlowStatusId int 
declare @ibWorkFlowStatusId int 
declare @ipWorkFlowStatusId int 

select 
    @draftWorkFlowStatusId = MAX(case when WFStep='DR' then WorkFlowId end),
    @diWorkFlowStatusId = MAX(case when WFStep='DI' then WorkFlowId end),
    @ibWorkFlowStatusId = MAX(case when WFStep='IB' then WorkFlowId end),
    @ipWorkFlowStatusId = MAX(case when WFStep='IP' then WorkFlowId end)
 from WorkFlow

您的 select as consturcted 一次只能获取一个变量值，因为每个变量每次都会针对每个记录进行评估，因此 - MAX()

如果有超过 1 条记录满足条件 WFStep = ? ，那么您应该告诉我们您想要其中哪一条。