查找数据库中带/不带前导零的重复项SQL查询失效问题

你的SQL查不到目标数据，核心问题是关联条件错误：A表的LOCID是带前导零的字符串（比如'00123'），B表的LOCID2是无前导零的字符串（比如'123'），直接用A.LOCID = B.LOCID2做字符串匹配，两者肯定不相等，自然查不出结果。

修正后的关联查询写法 #

把关联条件改为「A表LOCID去掉前导零后等于B表的LOCID2」，同时优化正则表达式（用+替代*，避免匹配空字符串）：

SELECT A.LOCID AS 带前导零的LOCID, B.LOCID2 AS 无前置零的LOCID
FROM (
    SELECT LOCID
    FROM TABLENAME
    WHERE REGEXP_LIKE(LOCID, '^[[:digit:]]+$') -- 确保是纯数字（排除空字符串）
      AND LOCID LIKE '0%'
) AS A
INNER JOIN (
    SELECT LOCID AS LOCID2
    FROM TABLENAME
    WHERE REGEXP_LIKE(LOCID, '^[[:digit:]]+$')
      AND LOCID NOT LIKE '0%'
) AS B
ON TRIM(LEADING '0' FROM A.LOCID) = B.LOCID2;

更简洁的分组统计写法 #

如果想一次性查看所有存在这类重复的分组，用分组查询更高效：

SELECT 
    TRIM(LEADING '0' FROM LOCID) AS 基准LOCID,
    LISTAGG(LOCID, ', ') WITHIN GROUP (ORDER BY LOCID) AS 所有重复项
FROM TABLENAME
WHERE REGEXP_LIKE(LOCID, '^[[:digit:]]+$')
GROUP BY TRIM(LEADING '0' FROM LOCID)
HAVING COUNT(DISTINCT LOCID) > 1;

这个写法会把「去掉前导零后值相同」的所有LOCID归为一组，只要组内有多个不同的LOCID（既有带前导零的也有不带的），就会被筛选出来，方便你一次性查看所有重复项。

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