SQL查询调整：统计列特定值，按Regular元素数量动态输出结果

可以直接在COUNT中加条件实现需求，核心用带条件的窗口计数函数按人员、公司、规则分组统计Regular元素数量，再根据数量过滤返回对应结果即可，修改后的完整查询如下：

WITH original_result AS (
    -- 封装原有UNION的查询逻辑
    SELECT person_number,
           company,
           rule_id,
           Sum(CASE
                 WHEN elements = '1_5X' THEN measure
               END) AS overtime_measure_hours,
           Sum(CASE
                 WHEN elements LIKE 'Regular%' THEN measure
               END) AS Reg_hours,
           hour     Hour_type,
           -- 统计当前组内Regular元素数量
           COUNT(CASE WHEN elements LIKE 'Regular%' THEN 1 END) OVER(PARTITION BY person_number, company, rule_id) AS regular_cnt
    FROM   (SELECT person_number,
                   company,
                   rule_id,
                   elements,
                   papf.hour
            FROM   per_all_people_f papf,
                   co_table co,
                   rule_table RULE,
                   time_track elements
            WHERE  elements.person_id = papf.person_id
                   AND co.co_id = papf.co_id
                   AND rule_table.rule_id = elements.rule_id)
    GROUP BY person_number, company, rule_id, hour
    UNION
    SELECT person_number,
           company,
           rule_id,
           Sum(CASE
                 WHEN elements = '1_5X' THEN measure
               END)      AS overtime_measure_hours,
           Sum(CASE
                 WHEN elements LIKE 'Regular%' THEN measure
               END)      AS Reg_hours,
           absences_name Hour_type,
           COUNT(CASE WHEN elements LIKE 'Regular%' THEN 1 END) OVER(PARTITION BY person_number, company, rule_id) AS regular_cnt
    FROM   (SELECT person_number,
                   company,
                   rule_id,
                   elements,
                   absences_name
            FROM   per_all_people_f papf,
                   co_table co,
                   rule_table RULE,
                   time_track elements,
                   absence_table ABSENCES_name
            WHERE  elements.person_id = papf.person_id
                   AND co.co_id = papf.co_id
                   AND rule_table.rule_id = elements.rule_id
                   AND ABSENCES_name.person_id = papf.person_id)
    GROUP BY person_number, company, rule_id, absences_name
)
SELECT person_number, company, rule_id, overtime_measure_hours, Reg_hours, 
       -- 数量为1时返回空的Hour_type
       CASE WHEN regular_cnt = 1 THEN NULL ELSE Hour_type END AS Hour_type
FROM original_result
-- 过滤符合要求的行：数量>2保留所有行，数量=1仅保留汇总行
WHERE (regular_cnt > 2) 
   OR (regular_cnt = 1 AND Hour_type IS NULL)

关键修改说明 #

• 用CTE封装原有查询逻辑，不需要改动已经验证正确的聚合规则
• COUNT(CASE WHEN elements LIKE 'Regular%' THEN 1 END) OVER(PARTITION BY person_number, company, rule_id) 就是带条件的组内计数，实现了Regular数量统计需求
• 最终查询根据计数结果分支返回，数量>2时保留所有带Hour_type的明细行，数量为1时仅返回不带Hour_type的汇总行，自动过滤冗余数据

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