SQL Server中CONCAT结合CASE WHEN报错及百分比输出问题求助

错误原因 #

• 语法层面：CAST 函数使用格式错误，正确语法为 CAST(待转换表达式 AS 目标类型)，原代码缺少右括号闭合CAST的参数部分，同时错误将分类字段[Demand Structure]作为数值参与占比计算。
• 逻辑层面：占比计算需要用对应分类的统计数量除以当前Model下所有分类的总计数，原代码的计算逻辑完全错误。

修正后的可用SQL #

SELECT
    Model,
    CONCAT(
        MAX(CASE WHEN ranking = 1 THEN [Demand Structure] END),
        ' ',
        ROUND(
            MAX(CASE WHEN ranking = 1 THEN Demand_Structure_count * 100.0 / total_model_count END),
            2
        ),
        '%'
    ) AS top1_Demand_Structure,
    CONCAT(
        MAX(CASE WHEN ranking = 2 THEN [Demand Structure] END),
        ' ',
        ROUND(
            MAX(CASE WHEN ranking = 2 THEN Demand_Structure_count * 100.0 / total_model_count END),
            2
        ),
        '%'
    ) AS top2_Demand_Structure
    -- INTO #demand_structure_temp
FROM
    (
        SELECT 
            Model,
            [Demand Structure],
            COUNT(1) AS Demand_Structure_count,
            -- 计算当前Model下的总样本数用于占比计算
            SUM(COUNT(1)) OVER(PARTITION BY Model) AS total_model_count,
            ROW_NUMBER() OVER(PARTITION BY Model ORDER BY COUNT(1) DESC) AS Ranking
        FROM [EDW].[sio].[TB_R_SURVEY_IN_OPERATION] 
        GROUP BY Model, [Demand Structure]
    ) a
GROUP BY Model
ORDER BY Model;

效果说明 #

运行后输出格式符合需求，示例：

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