处理政府工作报告

#查询数据库版本--

select version();

#清空数据重置自增字段临时用表导入数据用

truncate table `baogao_9999`;

ALTER TABLE `baogao_9999` AUTO_INCREMENT =1;

###获取字段名

DESC `baogao_9999` ;

#SELECT substring(MemoText,1,3) from baogao_9999 ; Caption

#SELECT * from baogao_9999 where substring(MemoText,1,3) = '国务院' ;

#查询非重复字段

SELECT DISTINCT idd FROM `baogao_9999`;

UPDATE baogao_9999 SET Caption= '国务院' where substring(MemoText,1,3) = '国务院';

UPDATE baogao_9999 SET Caption= '省政府' where substring(MemoText,1,3) <> '国务院';

UPDATE baogao_9999 SET Subject= '1' where LastModified = '总结';

UPDATE baogao_9999 SET Subject= '2' where LastModified = '部署';

UPDATE baogao_9999 SET Subject= '3' where LastModified = '开头';

UPDATE baogao_9999 SET Subject= '4' where LastModified = '结尾';

UPDATE baogao_9999 SET Subject= '5' where LastModified = '感谢';

UPDATE baogao_9999 SET Subject= '6' where LastModified = '问题';

UPDATE baogao_9999 SET Subject= '7' where LastModified = '体会';

UPDATE baogao_9999 SET Subject= '8' where LastModified = '要求';

UPDATE baogao_9999 SET HtmlMode= '11' where Subject= '1' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '12' where Subject= '2' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '13' where Subject= '3' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '14' where Subject= '4' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '15' where Subject= '5' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '16' where Subject= '6' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '17' where Subject= '7' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '18' where Subject= '8' and Caption='国务院' ;

UPDATE baogao_9999 SET HtmlMode= '21' where Subject= '1' and Caption='省政府' ;

UPDATE baogao_9999 SET HtmlMode= '22' where Subject= '2' and Caption='省政府' ;

UPDATE baogao_9999 SET HtmlMode= '23' where Subject= '3' and Caption='省政府' ;

UPDATE baogao_9999 SET HtmlMode= '24' where Subject= '4' and Caption='省政府' ;

UPDATE baogao_9999 SET HtmlMode= '25' where Subject= '5' and Caption='省政府' ;

UPDATE baogao_9999 SET HtmlMode= '26' where Subject= '6' and Caption='省政府' ;

UPDATE baogao_9999 SET HtmlMode= '27' where Subject= '7' and Caption='省政府' ;

UPDATE baogao_9999 SET HtmlMode= '28' where Subject= '8' and Caption='省政府' ;

UPDATE baogao_9999 SET idd= 'GWY' where MemoText like '国务院%' ;

UPDATE baogao_9999 SET idd= 'BJS' where MemoText like '北京%' ;

UPDATE baogao_9999 SET idd= 'TJS' where MemoText like '天津%' ;

UPDATE baogao_9999 SET idd= 'HBS' where MemoText like '河北%' ;

UPDATE baogao_9999 SET idd= 'SXS' where MemoText like '山西%' ;

UPDATE baogao_9999 SET idd= 'NMG' where MemoText like '内蒙古%' ;

UPDATE baogao_9999 SET idd= 'LNS' where MemoText like '辽宁%' ;

UPDATE baogao_9999 SET idd= 'JLS' where MemoText like '吉林%' ;

UPDATE baogao_9999 SET idd= 'HLJ' where MemoText like '黑龙江%' ;

UPDATE baogao_9999 SET idd= 'SHS' where MemoText like '上海%' ;

UPDATE baogao_9999 SET idd= 'JSS' where MemoText like '江苏%' ;

UPDATE baogao_9999 SET idd= 'ZJS' where MemoText like '浙江%' ;

UPDATE baogao_9999 SET idd= 'AHS' where MemoText like '安徽%' ;

UPDATE baogao_9999 SET idd= 'FJS' where MemoText like '福建%' ;

UPDATE baogao_9999 SET idd= 'JXS' where MemoText like '江西%' ;

UPDATE baogao_9999 SET idd= 'SDS' where MemoText like '山东%' ;

UPDATE baogao_9999 SET idd= 'HNS' where MemoText like '河南%' ;

UPDATE baogao_9999 SET idd= 'HBS' where MemoText like '湖北%' ;

UPDATE baogao_9999 SET idd= 'HNS' where MemoText like '湖南%' ;

UPDATE baogao_9999 SET idd= 'GDS' where MemoText like '广东%' ;

UPDATE baogao_9999 SET idd= 'GXS' where MemoText like '广西%' ;

UPDATE baogao_9999 SET idd= 'HNS' where MemoText like '海南%' ;

UPDATE baogao_9999 SET idd= 'CQS' where MemoText like '重庆%' ;

UPDATE baogao_9999 SET idd= 'SCS' where MemoText like '四川%' ;

UPDATE baogao_9999 SET idd= 'GZS' where MemoText like '贵州%' ;

UPDATE baogao_9999 SET idd= 'YNS' where MemoText like '云南%' ;

UPDATE baogao_9999 SET idd= 'XZS' where MemoText like '西藏%' ;

UPDATE baogao_9999 SET idd= 'SXS' where MemoText like '陕西%' ;

UPDATE baogao_9999 SET idd= 'GSS' where MemoText like '甘肃%' ;

UPDATE baogao_9999 SET idd= 'QHS' where MemoText like '青海%' ;

UPDATE baogao_9999 SET idd= 'NXS' where MemoText like '宁夏%' ;

UPDATE baogao_9999 SET idd= 'XJS' where MemoText like '新疆%' ;

#拼接字段

update baogao_9999 set UserID = concat(idd,"0",ID);

update baogao_9999 set QUserID = concat(`Subject`,CreateTime,idd,NID);

#先在baogao_9999 处理好数据，再导入baogao_2023

#查询最大值 6860

SELECT MAX(ID) AS max_ID FROM baogao_2023;

#表间数据复制

INSERT INTO baogao_2023(ID , NID , DocText , Description , MemoText , CreateTime , LastModified , Caption , Subject , HtmlMode , UserID , idd , QUserID) SELECT ID , NID , DocText , Description , MemoText , CreateTime , LastModified , Caption , Subject , HtmlMode , UserID , idd , QUserID FROM baogao_9999;

提示内容