如何在SQL中case when表达式中使用标量值函数

Question

SELECT a.UserID,
Sum(Case when UserStatus IN('Out of office','On vacation') then ... ELSE 0 END) as 'Total Out',
Sum(Case when UserStatus IN('Project X','Project Y','Project Z') then ... ELSE 0 END) as 'Total Project'
FROM UserLog a
WHERE a.DateColumn between @start AND @end GROUP BY a.UserID

这个查询给了我正确的答案。但问题出在case表达式中。我的变量是动态的，可以更改。所以我创建了一个包含子状态和基本状态的表

这是我桌子的一部分

BaseState           Substate
Total Out |    Out of office
Total Out |    On vacation
Total In  |    Project X
Total In  |    Project Y
Total In  |    Project Z
Total UK  |    Project X
Total UK  |    Project Y
.
.
.

为此，我尝试使用子查询，如下所示。

SELECT a.UserID,
Sum(Case when UserStatus IN(Select Substate FROM StateTable WHERE BaseState = 'Total Out') then ... ELSE 0 END) as 'Total Out',
Sum(Case when UserStatus IN(Select Substate FROM StateTable WHERE BaseState = 'Total In') then ... ELSE 0 END) as 'Total In'
FROM UserLog a
WHERE a.DateColumn between @start AND @end GROUP BY a.UserID

它会给出有关“无法对包含聚合或子查询表达式执行聚合函数”的错误。

我做了一个返回值的函数，比如‘不在办公室’，‘在度假’。我的函数如下

USE [AgentPerformance]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_Status]
(
    @gelen varchar(50)
)
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @donen NVARCHAR(200);
    Select @donen = Left(Main.[SubState],Len(Main.[SubState])-1)
From
    (
        Select distinct ST2.BaseStateName, 
            (
                Select ''''+RTRIM(ST1.SubStateName)+'''' + ',' AS [text()]
                From dbo.[SubState] ST1
                Where ST1.BaseStateName = ST2.BaseStateName
                ORDER BY ST1.BaseStateName
                For XML PATH ('')
            ) [SubState]
        From dbo.[SubState] ST2
    ) [Main] WHERE BaseStateName = @gelen


    RETURN @donen;
END

通常，当我使用Normally作为列时，它返回的结果与Total Out的'Out of office'，'On returning‘相同。但是，当我尝试写入IN时，同样的错误不能在包含聚合或子查询的表达式上执行聚合函数。

如何在SUM(CASE WHEN( logic )) <--逻辑中使用子查询或函数

Answer 1

与其尝试使用子查询或函数，为什么不使用连接

SELECT a.UserID,
Sum(Case when st.BaseState = 'Total Out' then ... ELSE 0 END) as 'Total Out',
Sum(Case when st.BaseState = 'Total In' then ... ELSE 0 END) as 'Total In'
FROM UserLog a
INNER JOIN StateTable st
ON a.UserStatus = st.Substate
WHERE a.DateColumn between @start AND @end
GROUP BY a.UserID

此时，您可能还需要考虑是否使用PIVOT来进一步简化当前通过SUM(CASE...执行的显式旋转