SQL中基于子串生成计算列及识别重复项的解决方案求助

数据集 #

需求说明 #

1. 创建Line Details计算列规则 #

• 若管线编号最后一个连字符（-）前的子串出现至少2次：
  • 图纸名称含05-13且管线编号含0513，标记为MATCH；
  • 图纸名称含05-13且管线编号含0511，标记为DUPLICATE；
  • 管线编号以E结尾，追加HEAT TRACE；
  • 管线编号以0"结尾，追加NON ISO；
• 若子串出现次数不足2次，标记为DO NOTHING。

2. 筛选重复子串的行 #

需筛选出管线编号最后一个连字符前的子串重复出现的行，示例：
输入：

2"-MSH-0513-16-S1-**1 1/2"A
2"-MSH-0513-16-S1-**2"AE
2"-MSH-0513-17-S1-**1 1/2"A
2"-MSH-0513-18-S1-**1 1/2"A
2"-FLW-0521-18-S1-**1"A
2"-FLW-0521-18-S1-**1"A

期望输出：

2"-MSH-0513-16-S1-**1 1/2"A
2"-MSH-0513-16-S1-**2"AE
2"-FLW-0521-18-S1-**1"A
2"-FLW-0521-18-S1-**1"A

问题与尝试 #

尝试以下SQL提取最后一个连字符前的子串，但报错'regex_count'不是已识别的内置函数名：

select SUBSTRING(LINE_NUM_CONCAT_, 
                 1, 
                 regexp_instr(LINE_NUM_CONCAT_, 
                              '-', 
                              1, 
                              regexp_count(LINE_NUM_CONCAT_, 
                              '-')
                  ) - 1)
FROM  PID_Components_PROCESS_LINES

解决方案 #

1. 替代regex_count的方法（适用于SQL Server等无regex_count的数据库） #

用LEN和REPLACE计算连字符的数量，替代regex_count：

SELECT 
    SUBSTRING(LINE_NUM_CONCAT_, 
              1, 
              CHARINDEX('-', LINE_NUM_CONCAT_, 
                        LEN(LINE_NUM_CONCAT_) - LEN(REPLACE(LINE_NUM_CONCAT_, '-', ''))
              ) - 1) AS prefix_substring
FROM PID_Components_PROCESS_LINES

解释：LEN(LINE_NUM_CONCAT_) - LEN(REPLACE(LINE_NUM_CONCAT_, '-', ''))计算出连字符的总个数，再通过CHARINDEX定位最后一个连字符的位置，最终提取出目标子串。

2. 完整实现计算列与筛选逻辑 #

步骤1：提取前缀并统计出现次数

用CTE先处理前缀子串和次数统计：

WITH LinePrefixes AS (
    SELECT 
        *,
        SUBSTRING(LINE_NUM_CONCAT_, 
                  1, 
                  CHARINDEX('-', LINE_NUM_CONCAT_, 
                            LEN(LINE_NUM_CONCAT_) - LEN(REPLACE(LINE_NUM_CONCAT_, '-', ''))
                  ) - 1) AS prefix_substring
    FROM PID_Components_PROCESS_LINES
),
PrefixCounts AS (
    SELECT 
        prefix_substring,
        COUNT(*) AS occurrence_count
    FROM LinePrefixes
    GROUP BY prefix_substring
)

步骤2：生成计算列并筛选重复行

SELECT 
    lp.图纸名称,
    lp.LINE_NUM_CONCAT_ AS 管线编号,
    -- 构建Line Details计算列
    CASE
        WHEN pc.occurrence_count >= 2 THEN
            CONCAT(
                CASE
                    WHEN lp.图纸名称 LIKE '%05-13%' AND lp.LINE_NUM_CONCAT_ LIKE '%0513%' THEN 'MATCH'
                    WHEN lp.图纸名称 LIKE '%05-13%' AND lp.LINE_NUM_CONCAT_ LIKE '%0511%' THEN 'DUPLICATE'
                    ELSE ''
                END,
                CASE
                    WHEN lp.LINE_NUM_CONCAT_ LIKE '%E' THEN ' / HEAT TRACE'
                    WHEN lp.LINE_NUM_CONCAT_ LIKE '%0"' THEN ' / NON ISO'
                    ELSE ''
                END
            ) AS [Line Details]
        ELSE 'DO NOTHING'
    END AS [Line Details],
    lp.prefix_substring
FROM LinePrefixes lp
JOIN PrefixCounts pc ON lp.prefix_substring = pc.prefix_substring
-- 筛选重复行（出现次数>=2）
WHERE pc.occurrence_count >= 2
ORDER BY lp.prefix_substring;

补充说明 #

• 若使用的是支持REGEXP_COUNT的数据库（如MySQL 8.0+、Oracle），可直接保留原逻辑，只需确认数据库版本支持该函数；
• 上述SQL以SQL Server语法为例，若为其他数据库，可调整字符串函数（比如MySQL用LOCATE替代CHARINDEX，REGEXP_INSTR优化定位逻辑）。

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