项目中所用到的mysql重复过滤

问题:首先用户会本地上传一批号码(可能重复)到我们项目,通过解析文件,把号码入库(只验证是不是号码其他不做改动)到号码表,然后对号码进行去重操作.

表结构为:主键(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;