DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_count_20161015`()
begin
declare v_sql varchar(1000);
declare j ,jmax,i,icnt int;
declare k int;
declare kword,jdisease_name,idisease_name,idcode varchar(1000);
-- set sql_safe_updates=0; --需要在外部执行下这句语句 mysql才能允许执行update 操作
set j=6986;
select max(rid) into jmax from tmp_disease_tb_b; -- tmp_disease_tb_b待判定的原始表
repeat
select disechiname into jdisease_name from tmp_disease_tb_b where rid=j;
truncate table tmp_count_tb; -- 辅助表
commit;
set k=1;
select substr(jdisease_name,k,1) into kword from dual;
repeat
set v_sql=concat('insert into tmp_count_tb select dcode,dname,''', kword,''' from tmp_disease_code5 where dname like ''%',kword,'%''');
-- tmp_disease_code5 是icd-10码表
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
set k=k+1;
select substr(jdisease_name,k,1) into kword from dual;
until (kword='')
end repeat;
select a.dis_name,count(*),a.dis_code into idisease_name ,icnt,idcode
from tmp_count_tb a
group by a.dis_name,a.dis_code order by count(*) desc,length(a.dis_name) asc limit 0,1;
commit;
update tmp_disease_tb_b set prd2=idisease_name,icnt=icnt,idcode=idcode where rid=j;
commit;
select j;
set j=j+1;
until j>jmax
end repeat;
end$$
DELIMITER ;
清洗数据:原始数据和码表比对中文字个数[mysql]
最新推荐文章于 2023-11-13 11:37:48 发布