如何编写SQL查询截断指定后缀查找重复数据

嘿，这个需求其实很好解决，核心就是先把所有带_xxx后缀的字段统一“清理”成不带后缀的形式，再通过分组统计找出重复项就行。我给你整理了几种常见数据库的实现方案，你可以根据自己用的数据库来选：

MySQL 实现 #

两种思路，一种是精准截取长度（性能更优），另一种是用正则替换（更灵活）：

方法1：精准截取 #

因为_xxx是固定4个字符的后缀，直接判断字段是否以它结尾，是的话就去掉最后4位：

SELECT 
  CASE 
    WHEN name LIKE '%_xxx' THEN SUBSTRING(name, 1, LENGTH(name) - 4)
    ELSE name
  END AS cleaned_name,
  COUNT(*) AS duplicate_count
FROM your_table
GROUP BY cleaned_name
HAVING COUNT(*) > 1;

方法2：正则替换 #

用正则匹配结尾的_xxx并替换为空，适合后续后缀规则可能微调的场景：

SELECT 
  REGEXP_REPLACE(name, '_xxx$', '') AS cleaned_name,
  COUNT(*) AS duplicate_count
FROM your_table
GROUP BY cleaned_name
HAVING COUNT(*) > 1;

PostgreSQL 实现 #

PostgreSQL的正则替换语法和MySQL类似，直接用REGEXP_REPLACE即可：

SELECT 
  REGEXP_REPLACE(name, '_xxx$', '') AS cleaned_name,
  COUNT(*) AS duplicate_count
FROM your_table
GROUP BY cleaned_name
HAVING COUNT(*) > 1;

SQL Server 实现 #

SQL Server可以用RIGHT和LEFT函数来处理：

SELECT 
  CASE 
    WHEN RIGHT(name, 4) = '_xxx' THEN LEFT(name, LEN(name) - 4)
    ELSE name
  END AS cleaned_name,
  COUNT(*) AS duplicate_count
FROM your_table
GROUP BY 
  CASE 
    WHEN RIGHT(name, 4) = '_xxx' THEN LEFT(name, LEN(name) - 4)
    ELSE name
  END
HAVING COUNT(*) > 1;

额外说明 #

• 把上述代码里的your_table替换成你实际的表名即可；
• 如果需要查看具体的重复记录（而不只是统计数），可以用子查询或者CTE关联原表，比如MySQL里可以这么写：

WITH cleaned_names AS (
  SELECT 
    name,
    REGEXP_REPLACE(name, '_xxx$', '') AS cleaned_name
  FROM your_table
)
SELECT *
FROM cleaned_names
WHERE cleaned_name IN (
  SELECT cleaned_name
  FROM cleaned_names
  GROUP BY cleaned_name
  HAVING COUNT(*) > 1
)
ORDER BY cleaned_name;

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