@wangyupu 2022-08-09T09:51:19.000000Z 字数 18157 阅读 38

广州医保DIP项目分类

在万达的每一天

建立dip原始表

CREATE TABLE `dip1_ys` (
  `zdbm` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `zdmc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `czbm` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `czmc` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
    index(zdbm)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;dip1_ysdip1_cf

删除诊断编码为空的字段信息

delete from dip1_ys where zdmc is null;

创建dip拆分表

CREATE TABLE `dip1_cf` (
  `zdbm` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `zdmc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `czbm` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `czmc` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `czglbm` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `czglmc` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `cfbz` int(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

操作原理

select substring_index(substring_index('82,83,84,85,86,87',',',help_topic_id+1),',',-1) as Id,
-- 使用substring_index截取字符串help_topic_id是从零开始记录的，代表分隔符，help_topic_id+1代表0+1 代表+1 字符串第一个分隔符之前的所有内容
 substring_index('82,83,84,85,86,87',',',help_topic_id +1) as A,
 -- 字符串长度为17 其中包含了间隔符逗号
 length('82,83,84,85,86,87') AS B,
 -- 除去间隔符之后的字段长度
 length(replace('82,83,84,85,86,87',',','')) AS C,
 -- 结果
 help_topic_id AS D,
 length('82,83,84,85,86,87')-length(replace('82,83,84,85,86,87',',',''))+1 AS F
from mysql.help_topic
 -- 确定循环次数字符串长度根据分隔符确定循环次数 一刀两半 两刀三半
where help_topic_id<(length('82,83,84,85,86,87')-length(replace('82,83,84,85,86,87',',',''))+1)

查询操作编码为null的字段信息进行转储 2445 条.

insert into dip1_cf select zdbm,zdmc,czbm,czmc,czbm,czmc,0 from dip1_ys where czbm is null

查询完毕之后进行删除操作 2445

delete from dip1_ys where czbm is null

向dip1_cf表中插入不包含/的数据共计 7175 条

insert into dip1_cf select zdbm,zdmc,czbm,czmc,czbm as czglbm,czmc as czglmc,1 as cfbz from dip1_ys where czbm not like '%/%';

操作完成之后进行将我们已经转换过的编码进行删除 7175

delete from dip1_ys where czbm not like '%/%'

等待上步操作完成之后进行下步操作更新单个操作，对单个操作组进行分析 2823 条数据

insert into dip1_cf
SELECT
    a.zdbm,
    a.zdmc,
        substring_index(substring_index( a.czbm,'/',b.help_topic_id + 1),'/' ,- 1) AS czbm,
        substring_index(substring_index( a.czmc,'/',b.help_topic_id + 1),'/' ,- 1) AS czmc,
        a.czbm as czglbm,
        a.czmc as czglmc,
        2
FROM
    dip1_ys a  
JOIN mysql.help_topic b ON b.help_topic_id <
(length(a.czbm) - length( replace(a.czbm, '/', '')  ) + 1)
where a.czbm  like '%/%' and a.czbm not like '%+%' 
order by a.zdbm,a.czbm;

拆分结束对我们的单个操作进行删除 916

DELETE from dip1_ys a where a.czbm  like '%/%' and a.czbm not like '%+%'

对A+C1/C2组合进行拆分 1018

insert into dip1_cf
SELECT
    a.zdbm,
    a.zdmc,
        concat(REVERSE(SUBSTRING(REVERSE(a.czbm), INSTR(REVERSE(a.czbm), '+'))),
               substring_index(substring_index( a.czbm,'/',b.help_topic_id + 1),'/' ,- 1)) AS czbm,
        concat(REVERSE(SUBSTRING(REVERSE(a.czmc), INSTR(REVERSE(a.czmc), '+'))),                
               substring_index(substring_index( a.czmc,'/',b.help_topic_id + 1),'/' ,- 1)) AS czmc,
        a.czbm as czglbm,
        a.czmc as czglmc,
        3
FROM
    dip1_ys a  
JOIN mysql.help_topic b ON b.help_topic_id <
(length(a.czbm) - length( replace(a.czbm, '/', '')  ) + 1)
where czbm  like '%/%' and czbm  like '%+%/%' and czbm not like '%/%+%/%' and czbm not like '%+%/%+%'
order by a.zdbm,a.czbm;

拆分结束对我们的A+C1/C2操作进行删除 386

DELETE FROM dip1_ys a  where czbm  like '%/%' and czbm  like '%+%/%' and czbm not like '%/%+%/%' and czbm not like '%+%/%+%'

对C1/C2+A+B 等进行拆分 1032

insert into dip1_cf
SELECT
    a.zdbm,
    a.zdmc,
        concat(substring_index(substring_index( substring_index(a.czbm,'+',1),'/',b.help_topic_id + 1),'/' ,- 1),
              substr(a.czbm,instr(a.czbm,'+'))) AS czbm,
        concat(substring_index(substring_index( substring_index(a.czmc,'+',1),'/',b.help_topic_id + 1),'/' ,- 1),
              substr(a.czmc,instr(a.czmc,'+'))) AS czmc,
        a.czbm as czglbm,
        a.czmc as czglmc,
        4
FROM
    dip1_ys a  
JOIN mysql.help_topic b ON b.help_topic_id <
(length(a.czbm) - length( replace(a.czbm, '/', '')  ) + 1)
where czbm  like '%/%' and czbm  like '%/%+%' and czbm not like '%+%/%+%' and czbm not like '%/%+%/%'
order by a.zdbm,a.czbm;

对C1/C2+A+B 等进行拆分过后进行删除操作 396

DELETE FROM dip1_ys a where czbm  like '%/%' and czbm  like '%/%+%' and czbm not like '%+%/%+%' and czbm not like '%/%+%/%'

对一个加号进行操作 901

insert into dip1_cf
select t1.zdbm,t1.zdmc,CONCAT_WS('+',t1.czbm1,t2.czbm1) as czbm,CONCAT_WS('+',t1.czmc1,t2.czmc1) as czmc,t1.czbm as czglbm , t1.czmc as czglmc, 5 from
(SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', 1 ) AS czbm1,substring_index( czmc, '+', 1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=1) a
    JOIN mysql.help_topic b ON b.help_topic_id < (length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t1,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1, 
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc, czbm,czmc, substring_index( czbm, '+', - 1 ) AS czbm1,substring_index( czmc, '+', - 1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=1 ) a
    JOIN mysql.help_topic b ON b.help_topic_id < (length( a.czbm1 ) - length(REPLACE ( a.czbm1, '/', '' ) ) + 1 )) t2
where t1.czbm1<>t2.czbm1
and t1.zdbm=t2.zdbm 
and t1.czbm=t2.czbm
order by zdbm,czbm

操作完成之后删除操作编码为 A1/A2 + B1/B2 数据进行删除 82

DELETE from dip1_ys a where length(czbm)-length(replace(czbm,'+',''))=1

对于两个加号 410

insert into dip1_cf 
select t1.zdbm,t1.zdmc,CONCAT_WS('+',t1.czbm1,t2.czbm1,t3.czbm1) as czbm,CONCAT_WS('+',t1.czmc1,t2.czmc1,t3.czmc1) as czmc,t1.czbm as czglbm , t1.czmc as czglmc, 5  from
(SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', 1 ) AS czbm1,substring_index( czmc, '+', 1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=2) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t1,
        (SELECT
 a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc 
FROM
    ( SELECT zdbm,zdmc, czbm,czmc, substring_index(substring_index( czbm, '+', 2 ),'+',-1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=2) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t2,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', - 1  ) AS czbm1,substring_index( czmc, '+', -1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=2) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t3
where t1.czbm1<>t2.czbm1<>t3.czbm1
and t1.zdbm=t2.zdbm
and t2.zdbm=t3.zdbm 
and t1.czbm=t2.czbm
and t2.czbm=t3.czbm
order by zdbm,czbm

针对已经转储的数据进行删除 84

DELETE from dip1_ys a where length(czbm)-length(replace(czbm,'+',''))=2

对三个加号进行比较

insert into dip1_cf
select t1.zdbm,t1.zdmc,CONCAT_WS('+',t1.czbm1,t2.czbm1,t3.czbm1,t4.czbm1) as czbm,CONCAT_WS('+',t1.czmc1,t2.czmc1,t1.czmc1,t4.czmc1) as czmc,t1.czbm as czglbm , t1.czmc as czglmc, 5  from
(SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', 1 ) AS czbm1,substring_index( czmc, '+', 1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=3) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t1,
        (SELECT
 a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc 
FROM
    ( SELECT zdbm,zdmc, czbm,czmc, substring_index(substring_index( czbm, '+', 2 ),'+',-1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=3) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t2,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index(substring_index( czbm, '+', -2 ),'+',1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=3) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t3,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', - 1  ) AS czbm1,substring_index( czmc, '+', -1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=3) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t4 
where t1.czbm1<>t2.czbm1<>t3.czbm1<>t4.czbm1
and t1.zdbm=t2.zdbm
and t2.zdbm=t3.zdbm 
and t1.czbm=t2.czbm
and t2.czbm=t3.czbm
and t3.czbm=t4.czbm
order by zdbm,czbm

对四个加号进行比较

DELETE from dip1_ys a where length(czbm)-length(replace(czbm,'+',''))=3
-- A+B+C+D+F  2166
INSERT INTO dip1_cf
select t1.zdbm,t1.zdmc,CONCAT_WS('+',t1.czbm1,t2.czbm1,t3.czbm1,t4.czbm1,t5.czbm1) as czbm,CONCAT_WS('+',t1.czmc1,t2.czmc1,t1.czmc1,t4.czmc1,t5.czmc1) as czmc,t1.czbm as czglbm , t1.czmc as czglmc, 5  from
(SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', 1 ) AS czbm1,substring_index( czmc, '+', 1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=4) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t1,
        (SELECT
 a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc 
FROM
    ( SELECT zdbm,zdmc, czbm,czmc, substring_index(substring_index( czbm, '+', 2 ),'+',-1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=4) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t2,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index(substring_index( czbm, '+', 3 ),'+',- 1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=4) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t3,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index(substring_index( czbm, '+', 4 ),'+',- 1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=4) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t4,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', - 1  ) AS czbm1,substring_index( czmc, '+', -1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=4) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t5
where t1.czbm1<>t2.czbm1<>t3.czbm1<>t4.czbm1<>t5.czbm1
and t1.zdbm=t2.zdbm
and t2.zdbm=t3.zdbm 
and t1.czbm=t2.czbm
and t2.czbm=t3.czbm
and t3.czbm=t4.czbm
and t4.czbm=t5.czbm
order by zdbm,czbm

删除操作

DELETE from dip1_ys a where length(czbm)-length(replace(czbm,'+',''))=4

5个+号

insert into dip1_cf
select t1.zdbm,t1.zdmc,CONCAT_WS('+',t1.czbm1,t2.czbm1,t3.czbm1,t4.czbm1,t5.czbm1,t6.czbm1) as czbm,CONCAT_WS('+',t1.czmc1,t2.czmc1,t1.czmc1,t4.czmc1,t5.czmc1,t6.czmc1) as czmc,t1.czbm as czglbm , t1.czmc as czglmc, 5  from
(SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', 1 ) AS czbm1,substring_index( czmc, '+', 1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=5) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t1,
        (SELECT
 a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc 
FROM
    ( SELECT zdbm,zdmc, czbm,czmc, substring_index(substring_index( czbm, '+', 2 ),'+',-1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=5) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t2,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index(substring_index( czbm, '+', 3 ),'+',- 1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=5) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t3,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index(substring_index( czbm, '+', 4 ),'+',- 1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=5) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t4,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index(substring_index( czbm, '+', 5 ),'+',- 1 )AS czbm1,substring_index(substring_index( czmc, '+', 2 ),'+',-1 )AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=5) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t5,
    (SELECT
  a.zdbm,
    a.zdmc,
    a.czbm,
    a.czmc,
    substring_index( substring_index( a.czbm1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc1, '/', b.help_topic_id + 1 ), '/',- 1 ) AS czmc1,
    a.czbm as czglbm,
    a.czmc as czglmc
FROM
    ( SELECT zdbm,zdmc,czbm,czmc, substring_index( czbm, '+', - 1  ) AS czbm1,substring_index( czmc, '+', -1 ) AS czmc1 FROM dip1_ys where length(czbm)-length(replace(czbm,'+',''))=5) a
    JOIN mysql.help_topic b ON b.help_topic_id < (
    length( a.czbm1 ) - length( REPLACE ( a.czbm1, '/', '' ) ) + 1 
    )) t6
where t1.czbm1<>t2.czbm1<>t3.czbm1<>t4.czbm1<>t5.czbm1<>t5.czbm1
and t1.zdbm=t2.zdbm
and t2.zdbm=t3.zdbm 
and t1.czbm=t2.czbm
and t2.czbm=t3.czbm
and t3.czbm=t4.czbm
and t4.czbm=t5.czbm
and t5.czbm=t6.czbm
order by zdbm,czbm

添加完成之后删除

DELETE from dip1_ys a where length(czbm)-length(replace(czbm,'+',''))=5

将拆分出来的数据存到test表中，并用concai_ws语句诊断编码使用加号与操作编码拼接起来插入到test语句中对该语句进行去重操作，操作完成之后接下列语句

insert into dip1_zc
SELECT distinct(test1),zdbm,
zdmc,
czbm,
czmc,
czglbm,
czglmc,
cfbz from 
test

-- 对包含+号数据进行人工拆分

insert into dip1_zctz
select t.zdbm,t.zdmc,t.czglbm,t.czglmc,GROUP_CONCAT(distinct t.czbm1 order by czbm1 separator '+' ) as czbm,GROUP_CONCAT(distinct t.czmc1 order by czbm1 separator '+') as czmc,'暂定','暂定',t.cfbz
from
(SELECT zdbm,czbm,zdmc,czmc,czglbm,czglmc,
    substring_index( substring_index( a.czbm, '+', b.help_topic_id + 1 ), '+',- 1 ) AS czbm1,
    substring_index( substring_index( a.czmc, '+', b.help_topic_id + 1 ), '+',- 1 ) AS czmc1,
    cfbz
  FROM dip1_zc a
    JOIN mysql.help_topic b ON b.help_topic_id < (length( a.czbm) - length( REPLACE ( a.czbm, '+', '' ) ) + 1 )
    where cfbz >= 3
) t
group by t.zdbm,t.czbm ;

-- 对不包含+号数据进行转存

insert into dip1_zctz
select t.zdbm,t.zdmc,t.czglbm,t.czglmc,t.czbm,t.czmc,'暂定','暂定',t.cfbz from dip1_zc t where cfbz<=2;

-- 病种组合编码根据拆分标志进行更新

UPDATE dip1_zctz a
set a.bzzhbm = CONCAT_WS(':',a.zdbm,'n(y)'),
    a.bzzhmc = CONCAT_WS(':',a.zdmc,'保守治疗'),
        a.cfbz = CONCAT_WS(':','0','操作编码为空')
where cfbz = 0;

UPDATE dip1_zctz 
set bzzhbm = CONCAT_WS(':',zdbm,czbm),
    bzzhmc = CONCAT_WS(':',zdmc,czmc)
where cfbz = '1';

UPDATE dip1_zctz 
set bzzhbm = CONCAT_WS(':',zdbm,czbm),
    bzzhmc = CONCAT_WS(':',zdmc,czmc)
where cfbz = '2';

UPDATE dip1_zctz 
set bzzhbm = CONCAT_WS(':',zdbm,czbm),
    bzzhmc = CONCAT_WS(':',zdmc,czmc)
where cfbz = '3';

UPDATE dip1_zctz 
set bzzhbm = CONCAT_WS(':',zdbm,czbm),
    bzzhmc = CONCAT_WS(':',zdmc,czmc)
where cfbz = '4';

UPDATE dip1_zctz 
set bzzhbm = CONCAT_WS(':',zdbm,czbm),
    bzzhmc = CONCAT_WS(':',zdmc,czmc)
where cfbz = '5';

UPDATE dip1_zctz 
set bzzhbm = CONCAT_WS(':',zdbm,czbm),
    bzzhmc = CONCAT_WS(':',zdmc,czmc)
where cfbz = '6';

UPDATE dip1_zctz 
set bzzhbm = CONCAT_WS(':',zdbm,czbm),
    bzzhmc = CONCAT_WS(':',zdmc,czmc)
where cfbz = '7';

UPDATE dip1_zctz a
set a.cfbz = CONCAT_WS(':','1','操作编码不包含/')
where a.cfbz = '1';

UPDATE dip1_zctz a
set a.cfbz = CONCAT_WS(':','2','操作编码只包含/')
where a.cfbz = '2';

UPDATE dip1_zctz a
set a.cfbz = CONCAT_WS(':','3','操作编码包含一个+')
where a.cfbz = '3';

UPDATE dip1_zctz a
set a.cfbz = CONCAT_WS(':','4','操作编码包含两个+')
where a.cfbz = '4';

UPDATE dip1_zctz a
set a.cfbz = CONCAT_WS(':','5','操作编码包含三个+')
where a.cfbz = '5';

UPDATE dip1_zctz a
set a.cfbz = CONCAT_WS(':','6','操作编码包含四个+')
where a.cfbz = '6';

UPDATE dip1_zctz a
set a.cfbz = CONCAT_WS(':','7','操作编码包含五个+')
where a.cfbz = '7';

广州医保DIP项目分类

建立dip原始表

删除诊断编码为空的字段信息

创建dip拆分表

操作原理

查询操作编码为null的字段信息 进行转储 2445 条.

查询完毕之后进行删除操作 2445

向dip1_cf表中插入不包含/的数据 共计 7175 条

操作完成之后进行将我们已经转换过的编码进行删除 7175

等待上步操作完成之后进行下步操作更新单个操作，对单个操作组进行分析 2823 条数据

拆分结束对我们的单个操作进行删除 916

对A+C1/C2组合进行拆分 1018

拆分结束对我们的A+C1/C2操作进行删除 386

对C1/C2+A+B 等进行拆分 1032

对C1/C2+A+B 等进行拆分过后进行删除操作 396

对一个加号进行操作 901

操作完成之后删除操作编码 为 A1/A2 + B1/B2 数据进行删除 82

对于两个加号 410

针对已经转储的数据进行删除 84

对三个加号进行比较

对四个加号进行比较

删除操作

5个+号

添加完成之后删除

将拆分出来的数据存到test表中，并用concai_ws语句诊断编码使用加号与操作编码拼接起来插入到test语句中对该语句进行去重操作，操作完成之后接下列语句

内容目录

查询操作编码为null的字段信息进行转储 2445 条.

向dip1_cf表中插入不包含/的数据共计 7175 条

操作完成之后删除操作编码为 A1/A2 + B1/B2 数据进行删除 82