1.获取省市区的字典.
从公开信息获取信息，以上面的数据为参考，见参考资料【1】
2.获取身份证列表(自己想办法)
3.字典使用excel的拆分单元格方式，拆成两行
4.取两行数据放入到notepad++或者其他工具，保存为.csv或者.txt格式
导入到mysql或者其他数据库
形成字典表
select SUBSTR(code FROM 1 FOR 2) as provinceCode,address as provinceName from address where code like '%0000';
形成省份地区关联表
insert INTO MappingCode(id_code,province,city)<br>select a.code,p.proviceName,CONCAT(p.proviceName,a.address) FROM address a<br>LEFT JOIN province p on SUBSTR(a.code FROM 1 FOR 2)=p.provinceCode
1.查询身份证所属省份
select pr.proviceName as '省份',ss.cnt as '身份信息的数量' from <br>(select t.province as provinceCode,count(t.province) as cnt from (select SUBSTR(idno FROM 1 FOR 2) as province from info ) t GROUP BY t.province <br>order by count(t.province) desc limit 1000) ss<br>LEFT JOIN province pr ON pr.provinceCode=ss.provinceCode <br>where pr.proviceName is not null<br>ORDER BY ss.cnt desc;
2.查询身份证所属地区
select mc.city as '地区',ss.cnt as '身份信息的数量' from <br>(select t.area as areaCode,count(t.area) as cnt from (select SUBSTR(idno FROM 1 FOR 6) as area from info ) t GROUP BY t.area <br>order by count(t.area) desc limit 1000) ss<br>LEFT JOIN MappingCode mc ON id_code=ss.areaCode <br>where mc.city is not null<br>ORDER BY ss.cnt desc;
参考资料
【1】https://www.cnblogs.com/wormday/articles/278709.html
Original: https://www.cnblogs.com/davidwang456/p/12453788.html
Author: 一天不进步，就是退步
Title: 根据身份证号码判定原籍地的方法
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/547641/
转载文章受原作者版权保护。转载请注明原作者出处！