基于sk_task_tab表的object_id公共前缀PL/SQL无变量查询需求

编号格式示例 #

格式一 #

所有实例的公共前缀为558.02，示例如下：

558.02 
558.02.01 
558.02.02 
558.02.07 
558.02.08 
558.02.06 

格式二 #

所有实例的公共前缀为555，示例如下：

555 
555.01 
555.04

业务场景 #

数据表sk_task_tab中，task_seq是唯一数值型字段，上述编号对应表内的object_id字段，一个task_seq关联多个object_id。

查询需求 #

编写无需声明变量的PL/SQL SELECT语句，根据指定task_seq，提取该任务下所有object_id的最长公共编号前缀，支持无点、1个点及多个点的格式场景。

方法一：逐字符对比法 #

通过遍历字符位置验证前缀通用性，最终取最长匹配项：

SELECT 
    SUBSTR(MIN(object_id), 1, 
        COALESCE(
            (SELECT MAX(LENGTH(common_prefix)) 
             FROM (
                 SELECT SUBSTR(MIN(o.object_id), 1, LEVEL) AS common_prefix
                 FROM sk_task_tab o
                 WHERE o.task_seq = :p_task_seq
                 CONNECT BY LEVEL <= LENGTH(MIN(o.object_id))
                 GROUP BY SUBSTR(MIN(o.object_id), 1, LEVEL)
                 HAVING COUNT(DISTINCT SUBSTR(o.object_id, 1, LEVEL)) = 1
             )
            ), 0
        )
    ) AS common_prefix
FROM sk_task_tab
WHERE task_seq = :p_task_seq;

逻辑说明

• 以当前task_seq下最短的object_id为基准，用CONNECT BY LEVEL遍历其每一个字符位置，生成所有可能的前缀。
• 对每个前缀分组，通过COUNT(DISTINCT ...) = 1判断该前缀是否存在于所有object_id中。
• 取最长的有效前缀作为结果，COALESCE处理无公共前缀的极端场景。

方法二：正则表达式法 #

利用字符串拼接和正则匹配，更简洁地提取最长公共前缀：

SELECT 
    REGEXP_SUBSTR(
        LISTAGG(object_id, '|') WITHIN GROUP (ORDER BY object_id),
        '^.*?(?=(?:\||[^.]\.)|$)'
    ) AS common_prefix
FROM sk_task_tab
WHERE task_seq = :p_task_seq;

逻辑说明

• 使用LISTAGG将同一task_seq下的object_id拼接为|分隔的字符串。
• 正则表达式^.*?(?=(?:\||[^.]\.)|$)匹配从开头到第一个分歧点的内容：
  • ^.*?：从字符串开头开始非贪婪匹配
  • (?=(?:\||[^.]\.)|$)：正向预查匹配|（分隔符）、非点字符后跟点（前缀结束标志）或字符串结尾
• 自动适配无点、单点、多点的格式场景。

使用说明 #

两种方法均直接通过:p_task_seq参数指定目标任务序列，无需提前声明变量，执行时传入具体数值即可。

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