如何优化TSQL存储过程：消除重复CASE语句

嗨SeanY！很高兴能帮你解决这个TSQL代码冗余的问题——重复写CASE语句确实太折磨人了，后续改逻辑要改好几处，很容易出错。咱们完全可以把这些计算逻辑只定义一次，然后在SELECT、GROUP BY和ORDER BY里复用，核心思路就是用公共表表达式（CTE）或者子查询先预计算好F_YEAR和F_Quarter，再在外层查询里直接引用。

方案一：用CTE（推荐，可读性更好） #

CTE就像是一个临时的结果集，只在当前查询中有效，我们可以把需要计算的字段先在这里处理好，外层直接用就行：

BEGIN
  WITH CalculatedCases AS (
    SELECT 
      dbo.TableA.[Account ID],
      dbo.TableA.TableA_ID,
      [Client Claims],
      [Finalised date],
      -- 只定义一次F_Quarter的计算逻辑
      CASE 
        WHEN [Finalised date] < DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 2, 7, 1) THEN 'PAST CASES'
        WHEN [Finalised date] BETWEEN DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 2, 7, 1) 
                                 AND DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 1, 6, 30) THEN 'YEAR'
        WHEN MONTH([Finalised date]) BETWEEN 1 AND 3 THEN ' Q3'
        WHEN MONTH([Finalised date]) BETWEEN 4 AND 6 THEN ' Q4'
        WHEN MONTH([Finalised date]) BETWEEN 7 AND 9 THEN ' Q1'
        WHEN MONTH([Finalised date]) BETWEEN 10 AND 12 THEN ' Q2' 
      END AS F_Quarter,
      -- 只定义一次F_YEAR的计算逻辑
      CASE 
        WHEN [Finalised date] < DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 2, 7, 1) THEN CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()) - 2) + ' & Older'
        WHEN MONTH([Finalised date]) BETWEEN 1 AND 6 THEN CONVERT(CHAR(4), YEAR([Finalised date]))
        WHEN MONTH([Finalised date]) BETWEEN 7 AND 12 THEN CONVERT(CHAR(4), YEAR([Finalised date]) + 1)
        ELSE CONVERT(CHAR(4), YEAR([Finalised date])) 
      END AS F_YEAR
    FROM dbo.TableB
    INNER JOIN dbo.TableA ON dbo.TableB.[Account ID] = dbo.TableA.[Account ID]
    LEFT OUTER JOIN dbo.Officers ON dbo.TableA.[Account Officer] = dbo.Officers.FullName
    WHERE [Case Officer] = @Officer_Name AND [Finalisation] IS NOT NULL
  )
  SELECT TOP (100) PERCENT
    COUNT(DISTINCT([Account ID])) AS Applications,
    SUM(CASE WHEN [Client Claims] LIKE '%claim%' THEN 1 ELSE 0 END) AS Main_Client,
    COUNT(TableA_ID) AS Clients,
    F_Quarter,
    F_YEAR
  FROM CalculatedCases
  GROUP BY F_YEAR, F_Quarter
  ORDER BY F_YEAR DESC, F_Quarter
END

方案二：用子查询（和CTE效果一致，写法不同） #

如果你不习惯用CTE，也可以把预计算的逻辑放在子查询里，外层查询直接基于子查询的结果操作：

BEGIN
  SELECT TOP (100) PERCENT
    COUNT(DISTINCT([Account ID])) AS Applications,
    SUM(CASE WHEN [Client Claims] LIKE '%claim%' THEN 1 ELSE 0 END) AS Main_Client,
    COUNT(TableA_ID) AS Clients,
    F_Quarter,
    F_YEAR
  FROM (
    SELECT 
      dbo.TableA.[Account ID],
      dbo.TableA.TableA_ID,
      [Client Claims],
      [Finalised date],
      CASE 
        WHEN [Finalised date] < DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 2, 7, 1) THEN 'PAST CASES'
        WHEN [Finalised date] BETWEEN DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 2, 7, 1) 
                                 AND DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 1, 6, 30) THEN 'YEAR'
        WHEN MONTH([Finalised date]) BETWEEN 1 AND 3 THEN ' Q3'
        WHEN MONTH([Finalised date]) BETWEEN 4 AND 6 THEN ' Q4'
        WHEN MONTH([Finalised date]) BETWEEN 7 AND 9 THEN ' Q1'
        WHEN MONTH([Finalised date]) BETWEEN 10 AND 12 THEN ' Q2' 
      END AS F_Quarter,
      CASE 
        WHEN [Finalised date] < DATEFROMPARTS(DATEPART(YEAR, GETDATE()) - 2, 7, 1) THEN CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()) - 2) + ' & Older'
        WHEN MONTH([Finalised date]) BETWEEN 1 AND 6 THEN CONVERT(CHAR(4), YEAR([Finalised date]))
        WHEN MONTH([Finalised date]) BETWEEN 7 AND 12 THEN CONVERT(CHAR(4), YEAR([Finalised date]) + 1)
        ELSE CONVERT(CHAR(4), YEAR([Finalised date])) 
      END AS F_YEAR
    FROM dbo.TableB
    INNER JOIN dbo.TableA ON dbo.TableB.[Account ID] = dbo.TableA.[Account ID]
    LEFT OUTER JOIN dbo.Officers ON dbo.TableA.[Account Officer] = dbo.Officers.FullName
    WHERE [Case Officer] = @Officer_Name AND [Finalisation] IS NOT NULL
  ) AS SubQuery
  GROUP BY F_YEAR, F_Quarter
  ORDER BY F_YEAR DESC, F_Quarter
END

额外优化建议 #

我还帮你把原来的日期拼接写法'07/01/' + CONVERT(VARCHAR(4), ...)改成了DATEFROMPARTS函数，这个函数能更安全地构造日期，避免因服务器日期格式设置不同导致的错误（比如如果服务器用MM/dd/yyyy格式，原来的写法会混淆月日顺序，用DATEFROMPARTS就不会有这个问题）。

现在你只需要在CTE/子查询里修改F_YEAR和F_Quarter的逻辑，SELECT、GROUP BY和ORDER BY都会自动同步，维护起来轻松多啦！

内容的提问来源于stack exchange，提问作者SeanY