如何移除SQL查询中重复的代码片段？寻求通用解决方法

你的查询里重复出现的LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1)确实可以被提取出来，既简化代码，又避免重复计算（虽然数据库可能会自动优化，但可读性提升是实打实的）。下面是几种通用的解决方案，适用于大多数SQL场景：

方法1：使用CTE（公共表表达式） #

这是最直观的方式，先把重复计算的字段封装到CTE里，后续查询直接引用别名：

WITH IdPrefixes AS (
    SELECT 
        [Id],
        LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1) AS IdPrefix
    FROM [dbo].[Ids]
)
SELECT 
    ISNULL(IdPrefix, 'Total'), 
    Count([Id]) 
FROM IdPrefixes 
GROUP BY ROLLUP(IdPrefix) 
ORDER BY 
    GROUPING(IdPrefix), 
    COUNT([Id]) DESC, 
    IdPrefix

CTE的优势在于逻辑清晰，适合单次查询内复用计算逻辑，而且对支持CTE的数据库（比如SQL Server、PostgreSQL、MySQL 8.0+）都适用。

方法2：使用子查询 #

如果你的数据库版本不支持CTE（比如老版本MySQL），子查询是另一个可靠的选择：

SELECT 
    ISNULL(IdPrefix, 'Total'), 
    Count([Id]) 
FROM (
    SELECT 
        [Id],
        LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1) AS IdPrefix
    FROM [dbo].[Ids]
) AS SubQuery
GROUP BY ROLLUP(IdPrefix) 
ORDER BY 
    GROUPING(IdPrefix), 
    COUNT([Id]) DESC, 
    IdPrefix

子查询的核心逻辑和CTE一致，只是写法不同，同样能实现只计算一次前缀、多次引用的效果。

方法3：使用CROSS APPLY（仅适用于SQL Server） #

如果你的计算逻辑更复杂，或者需要动态生成字段，CROSS APPLY是更灵活的选项：

SELECT 
    ISNULL(IdPrefix, 'Total'), 
    Count([Id]) 
FROM [dbo].[Ids]
CROSS APPLY (
    SELECT LEFT([Id], PATINDEX('%[0-9]%', [Id]) - 1) AS IdPrefix
) AS PrefixCalc
GROUP BY ROLLUP(IdPrefix) 
ORDER BY 
    GROUPING(IdPrefix), 
    COUNT([Id]) DESC, 
    IdPrefix

CROSS APPLY相当于行级别的计算，适合处理需要基于每行数据动态计算的场景，在这里也能很好地消除重复代码。

通用重构原则 #

不管用哪种方法，核心思路都是：

• 把重复的计算逻辑只定义一次，放在查询的“前置”部分（CTE、子查询、APPLY）
• 后续的SELECT、GROUP BY、ORDER BY等子句直接引用定义好的别名

这样做不仅让代码更易读、易维护，当你需要修改前缀提取逻辑时，只需要改一处即可，避免了多处修改遗漏的风险。

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