整理最新的行政区代码表步骤

整理最新的行政区代码表步骤
//---------------------------------------------------------------
从政府网上获取最新的行政区代码
http://www.stats.gov.cn/tjbz/xzqhdm/


拷贝到Excel文件中


然后再导入到Access数据库中Sheet1表


再生成lc_xzq_old表
SELECT mid$(fd1,1,6) as xzqdm,fd1 as xzqmc,mid$(fd1,1,6) as pid,mid(fd1,7) as xzqmc1 into lc_xzq_old
FROM Sheet1;


更新pid父级编号的方法
//更新地级市的pid方法
update lc_xzq_old set pid=mid(xzqdm,1,2)+"0000"
where mid(xzqdm,3,4)<>"0000" and mid(xzqdm,5,2)="00"


//更新县级市的pid方法
update lc_xzq_old set pid=mid(xzqdm,1,4)+"00"
where mid(xzqdm,3,4)<>"0000" and mid(xzqdm,5,2)<>"00"


再另存为lc_xzq表
select * into lc_xzq from lc_xzq_old


//看看where条件的数据是这样的嘛！是的!我反正看了，是这样子的情况
select *
from lc_xzq_old
where mid(xzqdm,3,4)<>"0000" and mid(xzqdm,5,2)<>"00"
where mid(xzqdm,3,4)<>"0000" and mid(xzqdm,5,2)="00"


//更新xzqmc行政区名称中有全角和半角空格字符的问题
update lc_xzq set xzqmc=replace(xzqmc," ","")




select replace(xzqmc," ","")
from lc_xzq 


select trim(replace(xzqmc," ",""))
from lc_xzq 


update lc_xzq set xzqmc=trim(replace(xzqmc," ",""))


//好了，OK了，lc_xzq表就是可以作为应用的数据库表了
---the---end---