Oracle中使用WITH CTE语句时GROUP BY未按预期聚合问题

出现同一个SPANID多次显示的问题，核心是GROUP BY的字段组合把相同SPANID拆分到了不同分组，具体原因和对应解决办法如下：

1. 日期字段（offered_date）存在差异 #

你的最终查询GROUP BY包含了offered_date（对应原表的created_date），如果同一个SPANID对应不同的创建日期（哪怕是同一天的不同时分秒），都会被分成单独的行。

解决办法： #

如果不需要按日期拆分数据，直接把offered_date从GROUP BY中移除，用聚合函数（如MAX/MIN）保留需要的日期值：

WITH
   temp
   AS
      (  SELECT TO_CHAR (tfc.spanid) spanid,              
                TO_CHAR (tfc.mz_code) AS maint_zone_code,
                TO_CHAR (tfc.mz_name) AS maint_zone_name,
                SUM (tfc.mho_handover_cert) AS ne_length,
                tfc.created_date AS offered_date          
           FROM app_lco.tbl_fip_checklist tfc
          WHERE     LENGTH (TRIM (tfc.spanid)) > 8
                AND LENGTH (TRIM (tfc.spanid)) < 21
                AND tfc.status = 'APPROVED'
       GROUP BY TO_CHAR (tfc.spanid),                     
                TO_CHAR (tfc.mz_code),
                TO_CHAR (tfc.mz_name),
                tfc.created_date              
       MINUS
       SELECT TO_CHAR (bb.link_id) AS span_id,
              TO_CHAR (bb.maintenancezonecode) AS maint_zone_code,
              TO_CHAR (bb.maintenancezonename) AS maint_zone_name,
              maint_zone_ne_span_length AS ne_length,
              bb.created_date
         FROM tbl_fiber_inv_jobs bb
        WHERE span_type = 'INTRACITY')
  SELECT spanid,
         maint_zone_code,
         maint_zone_name,
         SUM (ne_length),
         MAX(offered_date) AS latest_offered_date -- 取最新日期，也可用MIN取最早
    FROM temp
GROUP BY spanid,
         maint_zone_code,
         maint_zone_name;

如果只需要按年月日分组，忽略时分秒，用TRUNC函数处理日期字段：

WITH
   temp
   AS
      (  SELECT TO_CHAR (tfc.spanid) spanid,              
                TO_CHAR (tfc.mz_code) AS maint_zone_code,
                TO_CHAR (tfc.mz_name) AS maint_zone_name,
                SUM (tfc.mho_handover_cert) AS ne_length,
                TRUNC(tfc.created_date) AS offered_date -- 仅保留年月日
           FROM app_lco.tbl_fip_checklist tfc
          WHERE     LENGTH (TRIM (tfc.spanid)) > 8
                AND LENGTH (TRIM (tfc.spanid)) < 21
                AND tfc.status = 'APPROVED'
       GROUP BY TO_CHAR (tfc.spanid),                     
                TO_CHAR (tfc.mz_code),
                TO_CHAR (tfc.mz_name),
                TRUNC(tfc.created_date) -- 同步修改GROUP BY
       MINUS
       SELECT TO_CHAR (bb.link_id) AS span_id,
              TO_CHAR (bb.maintenancezonecode) AS maint_zone_code,
              TO_CHAR (bb.maintenancezonename) AS maint_zone_name,
              maint_zone_ne_span_length AS ne_length,
              TRUNC(bb.created_date) -- 另一表同步处理日期
         FROM tbl_fiber_inv_jobs bb
        WHERE span_type = 'INTRACITY')
  SELECT spanid,
         maint_zone_code,
         maint_zone_name,
         SUM (ne_length),
         offered_date
    FROM temp
GROUP BY spanid,
         maint_zone_code,
         maint_zone_name,
         offered_date;

2. 维护区域字段存在隐藏差异 #

maint_zone_code或maint_zone_name看起来相同，但实际有隐藏空格、大小写差异、全角/半角字符等，导致GROUP BY将其视为不同分组。

解决办法： #

在CTE中对这两个字段做统一格式化，去除空格并统一大小写：

WITH
   temp
   AS
      (  SELECT TO_CHAR (tfc.spanid) spanid,              
                TRIM(UPPER(TO_CHAR (tfc.mz_code))) AS maint_zone_code, -- 去空格+转大写
                TRIM(UPPER(TO_CHAR (tfc.mz_name))) AS maint_zone_name,
                SUM (tfc.mho_handover_cert) AS ne_length,
                tfc.created_date AS offered_date          
           FROM app_lco.tbl_fip_checklist tfc
          WHERE     LENGTH (TRIM (tfc.spanid)) > 8
                AND LENGTH (TRIM (tfc.spanid)) < 21
                AND tfc.status = 'APPROVED'
       GROUP BY TO_CHAR (tfc.spanid),                     
                TRIM(UPPER(TO_CHAR (tfc.mz_code))), -- 同步修改GROUP BY
                TRIM(UPPER(TO_CHAR (tfc.mz_name))),
                tfc.created_date              
       MINUS
       SELECT TO_CHAR (bb.link_id) AS span_id,
              TRIM(UPPER(TO_CHAR (bb.maintenancezonecode))) AS maint_zone_code, -- 另一表同步处理
              TRIM(UPPER(TO_CHAR (bb.maintenancezonename))) AS maint_zone_name,
              maint_zone_ne_span_length AS ne_length,
              bb.created_date
         FROM tbl_fiber_inv_jobs bb
        WHERE span_type = 'INTRACITY')
  SELECT spanid,
         maint_zone_code,
         maint_zone_name,
         SUM (ne_length),
         offered_date
    FROM temp
GROUP BY spanid,
         maint_zone_code,
         maint_zone_name,
         offered_date;

先排查再修改 #

不确定具体原因时，可先运行以下查询，查看同一个SPANID对应的分组字段是否真的存在差异：

SELECT spanid, offered_date, maint_zone_code, maint_zone_name, COUNT(*)
FROM temp
GROUP BY spanid, offered_date, maint_zone_code, maint_zone_name
ORDER BY spanid;

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