mysql处理单字段的若干场景

统计gl_org_device表中每个MAC地址出现的次数，并按出现次数从高到低排序

select mac , count(1) as mount from gl_org_device group by mac order by mount desc

将某个字段的空格去掉

UPDATE opr_user_fundation
SET custom_cell_num = REPLACE(custom_cell_num, ' ', '')

统计单个字段出现频率大于5的数据

如果只关心高频MAC地址，可增加HAVING子句：

SELECT mac, COUNT(1) AS mount 
FROM gl_org_device 
GROUP BY mac 
HAVING mount > 5  -- 只显示出现5次以上的MAC
ORDER BY mount DESC;

删除某字段重复的数据,只保留一行

WITH CTE AS (
    SELECT 
        id, 
        custom_name, 
        ROW_NUMBER() OVER (PARTITION BY custom_name ORDER BY id) AS row_num
    FROM opr_user_fundation
)
DELETE from  opr_user_fundation WHERE id in (select id FROM CTE WHERE row_num > 1)