基于变量动态构建SQL CASE语句的实现需求与问题

需求说明 #

需要实现基于变量动态调整的分桶统计：通过变量配置分桶的起始值、步长、桶数量，替代原查询中固定写死的CASE分桶逻辑，灵活统计Table R中Value字段的区间分布。

原固定逻辑查询：

SELECT subq.Bucket, COUNT(*) 'Count'
FROM
(
SELECT
  CASE
    WHEN R.Value < 10 THEN '0-10'
    WHEN R.Value Between 10 and 20 THEN '10-20'
    WHEN R.Value Between 20 and 30 THEN '20-30'
    WHEN R.Value Between 30 and 40 THEN '30-40'
    WHEN R.Value > 40  THEN '40+'
  END Bucket
FROM Table R
Where DateTime Between '2022-10-01' and '2022-11-10' and Type = 1
) subq
GROUP BY subq.Bucket

你尝试的WHILE循环写法存在语法错误——SQL查询语句中不能直接嵌套WHILE循环，需要改用以下两种可行方案：

方案一：动态SQL拼接CASE逻辑 #

通过变量拼接出完整的CASE语句，再执行动态SQL，完全灵活控制分桶规则：

DECLARE @NoRows INT = 5, @Range INT = 10, @StartRange INT = 0
DECLARE @CaseSQL NVARCHAR(MAX), @FullSQL NVARCHAR(MAX)

-- 初始化CASE语句的开头
SET @CaseSQL = 'CASE '

-- 循环拼接每个分桶的WHEN条件
WHILE @NoRows > 0
BEGIN
    SET @CaseSQL += 'WHEN R.Value BETWEEN ' + CAST(@StartRange AS NVARCHAR) + ' AND ' + CAST(@StartRange + @Range AS NVARCHAR) 
                  + ' THEN ''' + CAST(@StartRange AS NVARCHAR) + '-' + CAST(@StartRange + @Range AS NVARCHAR) + ''' '
    
    SET @StartRange += @Range
    SET @NoRows -= 1
END

-- 拼接最后一个"大于最大值"的桶
SET @CaseSQL += 'WHEN R.Value > ' + CAST(@StartRange AS NVARCHAR) + ' THEN ''' + CAST(@StartRange AS NVARCHAR) + '+'' END AS Bucket'

-- 拼接完整的查询语句
SET @FullSQL = '
SELECT subq.Bucket, COUNT(*) ''Count''
FROM
(
    SELECT ' + @CaseSQL + '
    FROM Table R
    Where DateTime Between ''2022-10-01'' and ''2022-11-10'' and Type = 1
) subq
GROUP BY subq.Bucket
'

-- 执行动态SQL
EXEC sp_executesql @FullSQL

说明 #

• 变量@NoRows控制桶的数量，@Range是步长，@StartRange是起始值
• 循环拼接每个分桶的WHEN分支，最后补充"大于最大区间值"的兜底分支
• 用sp_executesql执行拼接好的动态SQL，避免SQL注入风险

方案二：用数字辅助表实现无动态SQL的分桶 #

如果不想用动态SQL，可以通过生成连续的区间表，和原表关联实现分桶统计：

DECLARE @NoRows INT = 5, @Range INT = 10, @StartRange INT = 0

-- 生成数字辅助表（模拟连续区间）
WITH NumberCTE AS (
    SELECT 0 AS Num
    UNION ALL
    SELECT Num + 1 FROM NumberCTE WHERE Num < @NoRows
)
-- 生成区间表
, BucketCTE AS (
    SELECT 
        @StartRange + (Num * @Range) AS BucketStart,
        @StartRange + ((Num + 1) * @Range) AS BucketEnd,
        CAST(@StartRange + (Num * @Range) AS VARCHAR) + '-' + CAST(@StartRange + ((Num + 1) * @Range) AS VARCHAR) AS BucketName
    FROM NumberCTE
    UNION ALL
    -- 补充最后一个"大于最大值"的桶
    SELECT 
        @StartRange + (@NoRows * @Range) AS BucketStart,
        NULL AS BucketEnd,
        CAST(@StartRange + (@NoRows * @Range) AS VARCHAR) + '+' AS BucketName
)
-- 关联原表统计
SELECT 
    COALESCE(b.BucketName, '未匹配') AS Bucket,
    COUNT(r.Value) AS [Count]
FROM BucketCTE b
LEFT JOIN [Table] r 
    ON (b.BucketEnd IS NOT NULL AND r.Value BETWEEN b.BucketStart AND b.BucketEnd)
    OR (b.BucketEnd IS NULL AND r.Value > b.BucketStart)
WHERE r.DateTime Between '2022-10-01' and '2022-11-10' and r.Type = 1
GROUP BY b.BucketName
ORDER BY b.BucketStart

说明 #

• 用CTE生成连续数字，再转换成对应的分桶区间
• 通过LEFT JOIN关联原表，匹配对应的分桶
• 无需拼接SQL，直接通过变量控制区间规则，适合禁止动态SQL的场景

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