mysql - 使用 SQL 进行嵌套案例查询时出错

Question

尝试将以下查询转换为 SQL，查询在 MySQL 上运行良好。问题似乎是 CASE WHEN 区域字段我遇到了同样的错误。

show Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '='. Msg 156, Level 15, State 1, Line 47 Incorrect syntax near the keyword 'AND'. Msg 156, Level 15, State 1, Line 49 Incorrect syntax near the keyword 'ELSE'.

WHEN T.[StatusID] = 3
            THEN  
                CASE WHEN (((SELECT COUNT(TA1.[Approver_ID]) FROM [QESTORM].[dbo].[CR_TicketApproval] TA1
                    INNER JOIN [QESTORM].[dbo].[CR_ControlFlow_SubRoute] CFSR1 ON TA1.[SubRoute_ID] = CFSR1.[ID]
                    WHERE TA1.[Ticket_ID]= @iTkID AND TA1.Active=1  AND CFSR1.Active=1  AND CFSR1.[Sequence] =(SELECT CFSR2.[Sequence] FROM [QESTORM].[dbo].[CR_Ticket] T2  INNER JOIN [QESTORM].[dbo].[CR_ControlFlow_SubRoute] CFSR2 ON T2.[SubRouteID] = CFSR2.[ID]
                    WHERE T2.[ID] = @iTkID))<(SELECT COUNT(DISTINCT CFSR1.[ID])FROM [QESTORM].[dbo].[CR_Ticket]  AS T1     INNER JOIN [QESTORM].[dbo].[CR_ControlFlow_Route]  AS CFR1  ON T1.[FormID] = CFR1.[FormID] INNER JOIN [QESTORM].[dbo].[CR_ControlFlow_SubRoute]  AS CFSR1  ON CFR1.[ID] = CFSR1.[RouteID]
                    WHERE CFR1.[Active] = 1 AND CFSR1.[Active] = 1 AND T1.[ID] = @iTkID AND CFSR1.[Category] = 1 AND  CFSR1.[Sequence] = ( SELECT CFSR2.[Sequence] FROM [QESTORM].[dbo].[CR_Ticket]  AS T2 INNER JOIN [QESTORM].[dbo].[CR_ControlFlow_SubRoute]  AS CFSR2 ON T2.[SubRouteID] = CFSR2.[ID]
                    WHERE T2.[ID] = @iTkID))))

                THEN
                    CASE WHEN ((SELECT COUNT(1) FROM [QESTORM].[dbo].[CR_TicketApproval] WHERE [Ticket_ID]=@iTkID And [Active]=1) = 0) 
                        THEN 
            --ERROR SHOW HERE =>    ((T.[AuditUser_ID] = '444' OR T.[AuditUser_ID] IS NULL) AND (nx.actor = 2 OR appSameSeq.NTLogin=in_NTLogin)
                            AND nx.actor=3 
                            AND srSameSeq.subRouteID NOT IN (SELECT subRouteID FROM [QESTORM].[dbo].[CR_TicketApproval] WHERE [Ticket_ID]=@iTkID AND Active=1 )
                            AND appSameSeq.NTLogin=in_NTLogin 
                            AND nx.actor=3 AND srSameSeq.subRouteID NOT IN (SELECT subRouteID FROM [QESTORM].[dbo].[CR_TicketApproval] WHERE [Ticket_ID] = @iTkID AND Active = 1)
                        ELSE 0
                    END

Answer 1

我会把我的帽子扔进擂台。

该 SQL 语句可能存在不止一处错误。我要指出的是:

 CASE WHEN ((SELECT COUNT(1) FROM [QESTORM].[dbo].  [CR_TicketApproval] WHERE [Ticket_ID]=@iTkID And [Active]=1) = 0) 
     THEN 
        --ERROR SHOW HERE =>    ((T.[AuditUser_ID] = '444' OR T.[AuditUser_ID] IS NULL) AND (nx.actor = 2 OR appSameSeq.NTLogin=in_NTLogin)
                        AND nx.actor=3 
                        AND srSameSeq.subRouteID NOT IN (SELECT subRouteID FROM [QESTORM].[dbo].[CR_TicketApproval] WHERE [Ticket_ID]=@iTkID AND Active=1 )
                        AND appSameSeq.NTLogin=in_NTLogin 
                        AND nx.actor=3 AND srSameSeq.subRouteID NOT IN (SELECT subRouteID FROM [QESTORM].[dbo].[CR_TicketApproval] WHERE [Ticket_ID] = @iTkID AND Active = 1)
                    ELSE 0
                END

您是否尝试计算条件表达式，并将结果返回为 1 或 0，就像编程语言中的 bool 表达式一样？

这在 TSQL 中不起作用。这种表达式求值:

SET  @value = (1 > 0)

...将产生错误。您无法计算条件表达式:只能在测试中使用它，例如在 WHERE、HAVING 或 WHEN 子句中。

因此，如果这就是您正在做的事情，您最好将条件评估包装在另一个 CASE 语句中，如下所示:

THEN
    CASE WHEN  {complex conditional statement}
         THEN  1
         ELSE  0
    END
ELSE 
    0
END

另一件事:这是一个极其复杂的查询语句!我还没有对它进行足够的分析来看看是否可以简化它，但我建议您这样做，并着眼于使用通用表表达式来代替一些子查询。这可以使查询更容易理解(和调试)。