含前导零字符串型项目编号的重复统计问题求助

没问题，我来帮你搞定这个数据清理的需求！首先咱们先明确核心目标：按项目类型分组，统计「根编号相同」的项目数量——根编号要忽略尾随空格和前导零，同时兼容带字母的编号。

先分析你现有代码的小问题 #

你之前用LPAD(PROJ_NUM, 6, '0')来标准化编号，这个方法只适合纯数字的编号，如果遇到带字母的项目编号（比如00PROJ789），补前导零会破坏原始的字母结构，导致根编号判断错误。另外，处理尾随空格的逻辑也需要整合到标准化步骤里。

正确的解决方案 #

我们可以用正则表达式来精准处理前导零和尾随空格，再通过CTE（公共表表达式）先标准化所有编号，最后统计重复项。以下是适配大多数主流数据库（Oracle、MySQL、PostgreSQL等）的SQL：

WITH standardized_proj AS (
    SELECT
        PROJ_NUM,
        PROJ_TYPE,
        -- 1. 先去掉尾随空格；2. 去掉前导零；3. 全零/空值处理为'0'
        CASE
            WHEN REGEXP_REPLACE(RTRIM(PROJ_NUM), '^0+', '') = '' THEN '0'
            ELSE REGEXP_REPLACE(RTRIM(PROJ_NUM), '^0+', '')
        END AS root_proj_num
    FROM YOUR_TABLE_NAME
    WHERE PROJ_TYPE IS NOT NULL
)
SELECT
    sp.PROJ_NUM,
    sp.PROJ_TYPE,
    cnt.root_count AS COUNT
FROM standardized_proj sp
JOIN (
    -- 按项目类型+根编号分组，统计重复数量
    SELECT
        PROJ_TYPE,
        root_proj_num,
        COUNT(*) AS root_count
    FROM standardized_proj
    GROUP BY PROJ_TYPE, root_proj_num
    HAVING COUNT(*) > 1
) cnt ON sp.PROJ_TYPE = cnt.PROJ_TYPE AND sp.root_proj_num = cnt.root_proj_num
ORDER BY sp.PROJ_TYPE, sp.PROJ_NUM;

代码逻辑拆解 #

1. 标准化编号：

   • RTRIM(PROJ_NUM)：去掉项目编号的尾随空格
   • REGEXP_REPLACE(..., '^0+', '')：用正则去掉开头的所有前导零（不管有多少个）
   • CASE语句：如果处理后是空字符串（比如原编号是0000或全空格），则替换为'0'，避免空值影响统计

2. 统计重复项：

   • 子查询里按PROJ_TYPE和root_proj_num分组，统计每个组的数量，只保留数量>1的重复组
   • 最后关联回标准化后的原始数据，展示每个原始项目编号对应的重复数量

测试你的示例数据 #

比如你给出的样本：021523、000345、21523、345、00345（同PROJ_TYPE），处理后：

• 021523和21523的根编号都是21523，统计数量为2
• 000345、345、00345的根编号都是345，统计数量为3
  完全符合你的需求！

特殊情况兼容 #

• 带字母的编号：比如00PROJ123会被处理为PROJ123，A0012会被处理为A0012（只去掉开头的零，字母后的零保留）
• 全零编号：0000会被处理为0，避免空值

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