SQL如何将两个SELECT查询的计算结果作为新列追加到另一查询每行

需求背景 #

你此前已经实现了指定项目下指定进度类型的完成率计算，现在需要为指定项目的去重进度查询结果，每行追加对应进度类型的占比列。

原有代码参考 #

单进度类型占比计算代码 #

select
100.0 *
count(
    case when ISOGEN_LINE_PROGRESS_ = 'C'
    then 1
    end
)
   /
  nullif(count(
case when PROJECT_NUMBER_ = 'PJ001234'
then 1
end
), 0) as PERCENT_COMPLETE_BY_LINE_PROGRES_TYPE_
FROM dbo.PID_Components_PROCESS_LINES
WHERE ISOGEN_LINE_PROGRESS_ = 'C'
or PROJECT_NUMBER_ = 'PJ001234'

现有去重进度查询代码 #

SELECT DISTINCT ISOGEN_LINE_PROGRESS_, PROJECT_NUMBER_,
CASE
    WHEN ISOGEN_LINE_PROGRESS_ = 'A' THEN 'NOT IN MODEL'
    WHEN ISOGEN_LINE_PROGRESS_ = 'B' THEN 'ROUGHED IN'
    WHEN ISOGEN_LINE_PROGRESS_ = 'C' THEN 'PARTIAL CHECK'
    WHEN ISOGEN_LINE_PROGRESS_ = 'D' THEN 'READY FOR FINAL CHECK'
    WHEN ISOGEN_LINE_PROGRESS_ = 'E' THEN '100% COMPLETE'
    WHEN ISOGEN_LINE_PROGRESS_ = '0' THEN 'ISSUE FOR CONSTRUCTION'
END AS PROGRESS_PER_LINE_
FROM PID_Components_PROCESS_LINES
WHERE PROJECT_NUMBER_ = 'PJ001234'
ORDER BY ISOGEN_LINE_PROGRESS_

实现思路 #

使用窗口函数分别统计每个进度类型的行数、项目总匹配行数，计算占比后追加到结果列即可，无需额外子查询关联，性能更优。

最终实现SQL #

WITH line_counts AS (
    SELECT 
        ISOGEN_LINE_PROGRESS_,
        PROJECT_NUMBER_,
        CASE
            WHEN ISOGEN_LINE_PROGRESS_ = 'A' THEN 'NOT IN MODEL'
            WHEN ISOGEN_LINE_PROGRESS_ = 'B' THEN 'ROUGHED IN'
            WHEN ISOGEN_LINE_PROGRESS_ = 'C' THEN 'PARTIAL CHECK'
            WHEN ISOGEN_LINE_PROGRESS_ = 'D' THEN 'READY FOR FINAL CHECK'
            WHEN ISOGEN_LINE_PROGRESS_ = 'E' THEN '100% COMPLETE'
            WHEN ISOGEN_LINE_PROGRESS_ = '0' THEN 'ISSUE FOR CONSTRUCTION'
        END AS PROGRESS_PER_LINE_,
        -- 统计当前进度类型的行数
        COUNT(*) OVER (PARTITION BY ISOGEN_LINE_PROGRESS_) as type_cnt,
        -- 统计项目PJ001234的总行数
        COUNT(*) OVER () as total_cnt
    FROM PID_Components_PROCESS_LINES
    WHERE PROJECT_NUMBER_ = 'PJ001234'
)
SELECT DISTINCT 
    ISOGEN_LINE_PROGRESS_,
    PROJECT_NUMBER_,
    PROGRESS_PER_LINE_,
    -- 计算占比并拼接%符号，保留整数
    CONCAT(CAST(ROUND(100.0 * type_cnt / NULLIF(total_cnt, 0), 0) AS INT), '%') AS PERCENT_COMPLETE_BY_LINE_PROGRES_TYPE_
FROM line_counts
ORDER BY ISOGEN_LINE_PROGRESS_

代码说明 #

• 用CTE先预计算每个进度的行数和项目总行数，避免重复计算
• NULLIF(total_cnt, 0) 防止项目无数据时出现除以0的报错
• ROUND 函数可以根据需要调整保留的小数位数，当前配置为保留整数
• 最终返回结果和你预期的示例格式完全一致

内容的提问来源于stack exchange，提问作者Drafter-SQL