问题:首先用户会本地上传一批号码(可能重复)到我们项目,通过解析文件,把号码入库(只验证是不是号码其他不做改动)到号码表,然后对号码进行去重操作.
表结构为:主键(id),号码(mob),标识(phone_detail),时间(crt_time)等,表名:tb_phone
思路:将号码表做自身关联,由于主键(id)唯一,所以只保留重复号码的最小id即可;
具体sql如下:
SELECT
*
FROM
(
SELECT
a.id,
a.mob,
a.phone_detail
FROM
tb_phone a
WHERE
a.phone_detail IN ()
) aa
LEFT JOIN (
SELECT
min(b.id)
FROM
tb_phone b
WHERE
b.phone_detail IN ()
GROUP BY
b.mob
) bb ON aa.id = bb.id
WHERE
bb.id IS NULL;
Original: https://www.cnblogs.com/chujian007/p/16312103.html
Author: 初见洞洞拐
Title: 项目中所用到的mysql重复过滤
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/599594/
转载文章受原作者版权保护。转载请注明原作者出处!