当前位置：首页 > ops >正文

sql列中数据通过逗号分割的集合，对其中的值进行全表查重

ops 2025/6/17 11:23:08

来了来了，新的需求又双叒叕来了，在设计的逗号分割的集合中对值进行查重
通过前两篇文章，我们已经可以简单的实现查询、批量查询、剔除值等功能
跳转 sql列中数据通过逗号分割的集合，按需求剔除部分值

前置

假设表“scrm_customer”

id	mobile	other
1	155xxx8,171xxx2,187xxx6	null
2	155xxx8	null
3	155xxx9	null
4	187xxx6	null

我们对该表进行查重获取到重复的手机号

分析

假如我们的mobile不是,号分割的列，如何进行获取重复
我们可以通过 group by 对mobile进行分组，然后HAVING COUNT(mobile) > 1的

select mobile from scrm_customer c GROUP BY c.owner_id HAVING COUNT(mobile) > 1

我们获取到单表的重复项如此简单，那么，能否将mobile的值展开，然后进行HAVING COUNT(mobile) > 1

实现

第一步，获取到整表中单条数据的手机号最大数量

SELECT MAX((LENGTH(i.mobile) - LENGTH(REPLACE(i.mobile, ',', '')))) + 1 maxNub FROM scrm_customer i

第二步，构建WITH RECURSIVE 数字方案

WITH RECURSIVE numbers AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 6)
-- 得出传入的每一个值
SELECT n FROM numbers

在这里插入图片描述
我们将6替换成我们查询出来最大的手机号数量

第三步，使用CROSS JOIN 将原表将手机号的155xxx8,171xxx2,187xxx6拆分成三条数据，同时也会将单手机号的拆成三条

SELECT -- 通过 CROSS 对mobile进行截取，如果超过则取最后一个数值SUBSTRING_INDEX(SUBSTRING_INDEX(i.mobile, ',', n.n), ',', -1) AS new_mobile
FROM scrm_customer i
CROSS JOIN -- 第一步获取到的最大数量并拆成n条数据(WITH RECURSIVE numbers AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < (SELECT MAX((LENGTH(i.mobile) - LENGTH(REPLACE(i.mobile, ',', '')))) + 1 maxNub FROM scrm_customer i))SELECT  * FROM numbers) n

CROSS JOIN 是两个表关联的笛卡尔积，在其他地方慎用喔

SUBSTRING_INDEX：

SUBSTRING_INDEX(str, delim, count)

参数说明
str: 要处理的字符串。
delim: 分隔符，用于分割字符串。
count: 指定要提取的子字符串的索引。可以是正数或负数。
如果 count 是正数，SUBSTRING_INDEX 返回字符串中第 count 次出现分隔符之前的所有内容。
如果 count 是负数，SUBSTRING_INDEX 返回字符串中倒数第 count 次出现分隔符之后的所有内容。
我们可以写个例子试试

select SUBSTRING_INDEX("1,22,333,4444,55555,666666", ',', 3) nu1,
SUBSTRING_INDEX(SUBSTRING_INDEX("1,22,333,4444,55555,666666", ',', 3),',',2) nu2,
SUBSTRING_INDEX(SUBSTRING_INDEX("1,22,333,4444,55555,666666", ',', 3),',',-1) nu3
from dual;

将我们查出来的笛卡尔积进行处理，将id和mobile都是相同的数据排除出去，最终就是我们想要的mobile的展开数据

最简单的办法GROUP BY new_mobile,i.id

SELECT -- 通过 CROSS 对mobile进行截取，如果超过则取最后一个数值SUBSTRING_INDEX(SUBSTRING_INDEX(i.mobile, ',', n.n), ',', -1) AS new_mobile
FROM scrm_customer i
CROSS JOIN -- 第一步获取到的最大数量并拆成n条数据(WITH RECURSIVE numbers AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < (SELECT MAX((LENGTH(i.mobile) - LENGTH(REPLACE(i.mobile, ',', '')))) + 1 maxNub FROM scrm_customer i))SELECT  * FROM numbers) n
GROUP BY new_mobile,i.id

到这一步，我们已经获取到展开后的数据了，就可以用HAVING COUNT(mobile) > 1进行查重了

SELECT new_mobile FROM (	SELECT -- 通过 CROSS 对mobile进行截取，如果超过则取最后一个数值SUBSTRING_INDEX(SUBSTRING_INDEX(i.mobile, ',', n.n), ',', -1) AS new_mobileFROM scrm_customer iCROSS JOIN -- 第一步获取到的最大数量并拆成n条数据(WITH RECURSIVE numbers AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < (SELECT MAX((LENGTH(i.mobile) - LENGTH(REPLACE(i.mobile, ',', '')))) + 1 maxNub FROM scrm_customer i))SELECT  * FROM numbers) nGROUP BY new_mobile,i.id) tGROUP BY t.new_mobile
HAVING COUNT(t.new_mobile) > 1