中医风格网站模板,织梦源码官网,企业qq网页版,江苏省建设厅网站首页文章目录实际需求分析思路写拼接 SQL 脚本的脚本语句执行得到脚本语句保存成 SQL 脚本文件实际需求
有些行政区域的字段 area_fullname 是空的#xff0c;如何补全呢#xff1f;如下所示#xff1a;
分析思路
#xff08;一#xff09;如何取到每个区域的上级名称和上…
文章目录实际需求分析思路写拼接 SQL 脚本的脚本语句执行得到脚本语句保存成 SQL 脚本文件实际需求
有些行政区域的字段 area_fullname 是空的如何补全呢如下所示
分析思路
一如何取到每个区域的上级名称和上上级名称 区域编码有规律末尾有2个0的是上级末尾有3个0 的是上上级从而可以通过截取字符串、拼接字符串、关联子查询来得到。
二用到哪些技术点
截取字符串函数拼接字符串函数空值转换函数关联子查询
上级(ifnull((select b.area_name from td_area_test b where b.area_code concat(left(a.area_code,4),00)),)) superior上上级(ifnull((select c.area_name from td_area_test c where c.area_code concat(left(a.area_code,3),000)),)) superlative本级a.area_name inferior把 area_fullname 为空的区域的名称上级名称上上级名称查询出来语句如下
select a.area_code,a.area_name as inferior,
(ifnull((select b.area_name from td_area_test b
where b.area_code concat(left(a.area_code,4),00)),)) superior,
(ifnull((select c.area_name from td_area_test c
where c.area_code concat(left(a.area_code,3),000)),)) superlative
from td_area_test a where a.area_fullname ;写拼接 SQL 脚本的脚本语句
批量生成 SQL 脚本语句的脚本语句如下
select concat(update td_area_test set area_fullname ,(concat((ifnull((select c.area_name from td_area_test c
where c.area_code concat(left(a.area_code,3),000)),)),(ifnull((select b.area_name from td_area_test b where
b.area_code concat(left(a.area_code,4),00)),)),a.area_name)), where area_code ,a.area_code,;)
sqlsentence from td_area_test a where a.area_fullname ;执行得到脚本语句
执行上述脚本语句后得到如下结果
mysql select concat(update td_area_test set area_fullname ,(concat((ifnull((select c.area_name from td_area_test c where c.area_code concat(left(a.area_code,3),000)),)),(ifnull((select b.area_name from td_area_test b where b.area_code concat(left(a.area_code,4),00)),)),a.area_name)), where area_code ,a.area_code,;) sqlsentence from td_area_test a where a.area_fullname ;
---------------------------------------------------------------------------------------------------------------------------------------------------------
| sqlsentence |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| update td_area_test set area_fullname 内蒙古自治区赤峰市敖汉旗 where area_code 150430; |
| update td_area_test set area_fullname 锡林郭勒盟正蓝旗 where area_code 152530; |
| update td_area_test set area_fullname 黑龙江省哈尔滨市香坊区 where area_code 230110; |
| update td_area_test set area_fullname 黑龙江省齐齐哈尔市克东县 where area_code 230230; |
| update td_area_test set area_fullname 黑龙江省伊春市五营区 where area_code 230710; |
| update td_area_test set area_fullname 上海市杨浦区 where area_code 310110; |
| update td_area_test set area_fullname 上海市奉贤区 where area_code 310120; |
| update td_area_test set area_fullname 上海市崇明县 where area_code 310230; |
| update td_area_test set area_fullname 江苏省淮安市盱眙县 where area_code 320830; |
| update td_area_test set area_fullname 浙江省杭州市余杭区 where area_code 330110; |
| update td_area_test set area_fullname 罗津 where area_code 342201; |
| update td_area_test set area_fullname 福建省三明市建宁县 where area_code 350430; |
| update td_area_test set area_fullname 江西省九江市彭泽县 where area_code 360430; |
| update td_area_test set area_fullname 江西省赣州市宁都县 where area_code 360730; |
| update td_area_test set area_fullname 江西省吉安市永新县 where area_code 360830; |
| update td_area_test set area_fullname 抚州市抚州市广昌县 where area_code 361030; |
| update td_area_test set area_fullname 抚州市上饶市婺源县 where area_code 361130; |
| update td_area_test set area_fullname 山东省济宁市汶上县 where area_code 370830; |
| update td_area_test set area_fullname 许昌市南阳市桐柏县 where area_code 411330; |
| update td_area_test set area_fullname 湖南省长沙市安居区 where area_code 430185; |
| update td_area_test set area_fullname 湖南省株洲市芦松区 where area_code 430282; |
| update td_area_test set area_fullname 湖南省常德市贺家山原种场 where area_code 430782; |
| update td_area_test set area_fullname 湖南省常德市德山开发区 where area_code 430783; |
| update td_area_test set area_fullname 湖南省常德市西湖管理区 where area_code 430784; |
| update td_area_test set area_fullname 湖南省常德市西洞庭管理区 where area_code 430785; |
| update td_area_test set area_fullname 湖南省益阳市大通湖区 where area_code 430940; |
| update td_area_test set area_fullname 郴州市怀化通道侗族自治县 where area_code 431230; |
| update td_area_test set area_fullname 郴州市怀化洪江区 where area_code 431282; |
| update td_area_test set area_fullname 湘西土家族苗族自治州龙山县 where area_code 433130; |
| update td_area_test set area_fullname 广西壮族自治区桂林市平乐县 where area_code 450330; |
| update td_area_test set area_fullname 百色市百色市西林县 where area_code 451030; |
| update td_area_test set area_fullname 省直辖县级行政区划省直辖县级行政区划琼中黎族苗族自治县 where area_code 469030; |
| update td_area_test set area_fullname 重庆市万盛区 where area_code 500110; |
| update td_area_test set area_fullname 重庆市丰都县 where area_code 500230; |
| update td_area_test set area_fullname 重庆市石柱土家族自治县 where area_code 500240; |
| update td_area_test set area_fullname 阿坝藏族羌族自治州壤塘县 where area_code 513230; |
| update td_area_test set area_fullname 甘孜藏族自治州德格县 where area_code 513330; |
| update td_area_test set area_fullname 凉山彝族自治州金阳县 where area_code 513430; |
| update td_area_test set area_fullname 贵州省遵义市习水县 where area_code 520330; |
| update td_area_test set area_fullname 铜仁地区万山特区 where area_code 522230; |
| update td_area_test set area_fullname 黔东南苗族侗族自治州台江县 where area_code 522630; |
| update td_area_test set area_fullname 黔南布依族苗族自治州龙里县 where area_code 522730; |
| update td_area_test set area_fullname 云南省昭通市水富县 where area_code 530630; |
| update td_area_test set area_fullname 红河哈尼族彝族自治州金平苗族瑶族傣族自治县 where area_code 532530; |
| update td_area_test set area_fullname 大理白族自治州洱源县 where area_code 532930; |
| update td_area_test set area_fullname 日喀则地区仁布县 where area_code 542330; |
| update td_area_test set area_fullname 那曲地区尼玛县 where area_code 542430; |
| update td_area_test set area_fullname 陕西省宝鸡市凤县 where area_code 610330; |
| update td_area_test set area_fullname 陕西省咸阳市淳化县 where area_code 610430; |
| update td_area_test set area_fullname 陕西省延安市宜川县 where area_code 610630; |
| update td_area_test set area_fullname 陕西省汉中市佛坪县 where area_code 610730; |
| update td_area_test set area_fullname 陕西省榆林市清涧县 where area_code 610830; |
| update td_area_test set area_fullname 克孜勒苏柯尔克孜自治州喀什地区巴楚县 where area_code 653130; |
---------------------------------------------------------------------------------------------------------------------------------------------------------
53 rows in set (0.01 sec)保存成 SQL 脚本文件
上述生成的SQL 脚本语句直接从命令行复制粘贴到脚本文件中要注意清除掉多余的符号 | 然后在命令终端执行脚本文件即可清理后如下
update td_area_test set area_fullname 内蒙古自治区赤峰市敖汉旗 where area_code 150430;
update td_area_test set area_fullname 锡林郭勒盟正蓝旗 where area_code 152530;
update td_area_test set area_fullname 黑龙江省哈尔滨市香坊区 where area_code 230110;
update td_area_test set area_fullname 黑龙江省齐齐哈尔市克东县 where area_code 230230;
update td_area_test set area_fullname 黑龙江省伊春市五营区 where area_code 230710;
update td_area_test set area_fullname 上海市杨浦区 where area_code 310110;
update td_area_test set area_fullname 上海市奉贤区 where area_code 310120;
update td_area_test set area_fullname 上海市崇明县 where area_code 310230;
update td_area_test set area_fullname 江苏省淮安市盱眙县 where area_code 320830;
update td_area_test set area_fullname 浙江省杭州市余杭区 where area_code 330110;
update td_area_test set area_fullname 罗津 where area_code 342201;
update td_area_test set area_fullname 福建省三明市建宁县 where area_code 350430;
update td_area_test set area_fullname 江西省九江市彭泽县 where area_code 360430;
update td_area_test set area_fullname 江西省赣州市宁都县 where area_code 360730;
update td_area_test set area_fullname 江西省吉安市永新县 where area_code 360830;
update td_area_test set area_fullname 抚州市抚州市广昌县 where area_code 361030;
update td_area_test set area_fullname 抚州市上饶市婺源县 where area_code 361130;
update td_area_test set area_fullname 山东省济宁市汶上县 where area_code 370830;
update td_area_test set area_fullname 许昌市南阳市桐柏县 where area_code 411330;
update td_area_test set area_fullname 湖南省长沙市安居区 where area_code 430185;
update td_area_test set area_fullname 湖南省株洲市芦松区 where area_code 430282;
update td_area_test set area_fullname 湖南省常德市贺家山原种场 where area_code 430782;
update td_area_test set area_fullname 湖南省常德市德山开发区 where area_code 430783;
update td_area_test set area_fullname 湖南省常德市西湖管理区 where area_code 430784;
update td_area_test set area_fullname 湖南省常德市西洞庭管理区 where area_code 430785;
update td_area_test set area_fullname 湖南省益阳市大通湖区 where area_code 430940;
update td_area_test set area_fullname 郴州市怀化通道侗族自治县 where area_code 431230;
update td_area_test set area_fullname 郴州市怀化洪江区 where area_code 431282;
update td_area_test set area_fullname 湘西土家族苗族自治州龙山县 where area_code 433130;
update td_area_test set area_fullname 广西壮族自治区桂林市平乐县 where area_code 450330;
update td_area_test set area_fullname 百色市百色市西林县 where area_code 451030;
update td_area_test set area_fullname 省直辖县级行政区划省直辖县级行政区划琼中黎族苗族自治县 where area_code 469030;
update td_area_test set area_fullname 重庆市万盛区 where area_code 500110;
update td_area_test set area_fullname 重庆市丰都县 where area_code 500230;
update td_area_test set area_fullname 重庆市石柱土家族自治县 where area_code 500240;
update td_area_test set area_fullname 阿坝藏族羌族自治州壤塘县 where area_code 513230;
update td_area_test set area_fullname 甘孜藏族自治州德格县 where area_code 513330;
update td_area_test set area_fullname 凉山彝族自治州金阳县 where area_code 513430;
update td_area_test set area_fullname 贵州省遵义市习水县 where area_code 520330;
update td_area_test set area_fullname 铜仁地区万山特区 where area_code 522230;
update td_area_test set area_fullname 黔东南苗族侗族自治州台江县 where area_code 522630;
update td_area_test set area_fullname 黔南布依族苗族自治州龙里县 where area_code 522730;
update td_area_test set area_fullname 云南省昭通市水富县 where area_code 530630;
update td_area_test set area_fullname 红河哈尼族彝族自治州金平苗族瑶族傣族自治县 where area_code 532530;
update td_area_test set area_fullname 大理白族自治州洱源县 where area_code 532930;
update td_area_test set area_fullname 日喀则地区仁布县 where area_code 542330;
update td_area_test set area_fullname 那曲地区尼玛县 where area_code 542430;
update td_area_test set area_fullname 陕西省宝鸡市凤县 where area_code 610330;
update td_area_test set area_fullname 陕西省咸阳市淳化县 where area_code 610430;
update td_area_test set area_fullname 陕西省延安市宜川县 where area_code 610630;
update td_area_test set area_fullname 陕西省汉中市佛坪县 where area_code 610730;
update td_area_test set area_fullname 陕西省榆林市清涧县 where area_code 610830;
update td_area_test set area_fullname 克孜勒苏柯尔克孜自治州喀什地区巴楚县 where area_code 653130;
