DROP TABLE IF EXISTS temp_dx_main; DROP TABLE IF EXISTS temp_dx; DROP TABLE IF EXISTS temp_dx1; DROP TABLE IF EXISTS temp_dx2; DROP TABLE IF EXISTS temp_gx; DROP TABLE IF EXISTS temp_gx_ls; DROP TABLE IF EXISTS temp_yh; DROP TABLE IF EXISTS temp_tel; DROP TABLE IF EXISTS temp_gx2; DROP TABLE IF EXISTS temp_clean_dx; DROP TABLE IF EXISTS temp_clean_gx; CREATE TEMPORARY TABLE `temp_clean_dx`( `id` bigint(20) NOT NULL AUTO_INCREMENT, `case_id` bigint(20) DEFAULT NULL , `group_id` bigint(20) DEFAULT NULL , `clazz` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL , `type` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL , `obj` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL , `mc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL , `sm` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, KEY `idx_case_id` (`case_id`) USING BTREE) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; ALTER TABLE `temp_clean_dx` ADD INDEX idx_clazz(`clazz`); ALTER TABLE `temp_clean_dx` ADD INDEX idx_obj (`obj`); INSERT INTO temp_clean_dx(case_id, group_id, clazz, type, obj, mc, sm)(select case_id, group_id, clazz, type, obj, mc, sm from clean_table_dx where case_id = 6); create TEMPORARY table temp_clean_gx( id bigint NOT NULL AUTO_INCREMENT, case_id bigint null, tb_gx_id bigint null comment '关联关系id', obj1 varchar(500) null comment '对象', obj2 varchar(500) null comment '对象', gx_mc varchar(255) null comment '关系描述', clazz varchar(20) null comment '关系类型sfz_card', num varchar(20) null comment '统计数量', sm varchar(255) null comment '对这个数量的描述', sj datetime null comment '发生时间', team_id varchar(255) null comment '组id', ext1 varchar(255) null comment '扩展字段1', ext2 varchar(255) null comment '扩展字段2', sbxq int(11) null,sjfx int(11) null,obj1_name varchar(150) null,obj2_name varchar(150) null,cs int(11) null, PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; ALTER TABLE `temp_clean_gx` ADD INDEX idx_clazz(`clazz`); ALTER TABLE `temp_clean_gx` ADD INDEX idx_obj1(`obj1`); ALTER TABLE `temp_clean_gx` ADD INDEX idx_obj2(`obj2`); INSERT INTO temp_clean_gx(case_id, tb_gx_id, obj1, obj2, gx_mc, clazz, num, sm, obj1_name, obj2_name, sbxq, cs, sjfx)(Select case_id, tb_gx_id, obj1, obj2, gx_mc, clazz, num, sm, obj1_name, obj2_name, sbxq, cs, sjfx from clean_table_gx where case_id = 6); CREATE TEMPORARY TABLE IF NOT EXISTS temp_dx_main( id BIGINT(20) NOT NULL AUTO_INCREMENT, team_id VARCHAR(50) DEFAULT NULL, bm VARCHAR(200) DEFAULT NULL, class VARCHAR(200) DEFAULT NULL, mc VARCHAR(200) DEFAULT NULL, sfz VARCHAR(200) DEFAULT NULL, sm VARCHAR(2000) DEFAULT NULL, dxId BIGINT(20) DEFAULT NULL,fzId BIGINT(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; INSERT INTO temp_dx_main(class, bm, mc, sm, dxId, fzId) SELECT max(IF(obj_type = 1, 'sfz', 'ins')), max(IF(obj_type = 1, obj_type_no, obj_name)), max(obj_name), '' AS sm, id, obj_group FROM case_obj WHERE case_id = 6 GROUP BY obj_type_no, obj_name; INSERT INTO temp_dx_main(class, bm, mc, sm) SELECT max(clazz), obj, max(mc), max(CAST(sm as CHAR(2000))) FROM temp_clean_dx WHERE clazz IN ('sfz', 'ins') GROUP BY obj; DELETE FROM temp_dx_main WHERE bm is null OR bm NOT IN ('513126197412174415', '成都市章大妈农副产品有限公司', '110101199003071233', '330382199003081853', '510183198103160051'); CREATE TEMPORARY TABLE IF NOT EXISTS temp_dx( id BIGINT(20) NOT NULL AUTO_INCREMENT, team_id VARCHAR(50) DEFAULT NULL, class VARCHAR(20) DEFAULT NULL, type VARCHAR(50) DEFAULT NULL, bm VARCHAR(500) DEFAULT NULL, mc VARCHAR(200) DEFAULT NULL, sfz VARCHAR(50) DEFAULT NULL, zp VARCHAR(100) DEFAULT NULL, sm VARCHAR(2000) DEFAULT NULL, main VARCHAR(20) DEFAULT NULL, kg VARCHAR(100) DEFAULT NULL, dxId BIGINT(20) DEFAULT NULL,fzId BIGINT(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; CREATE TEMPORARY TABLE IF NOT EXISTS temp_dx2 ( id BIGINT(20) NOT NULL, team_id VARCHAR(50) DEFAULT NULL, class VARCHAR(20) DEFAULT NULL, type VARCHAR(50) DEFAULT NULL, bm VARCHAR(200) DEFAULT NULL, mc VARCHAR(200) DEFAULT NULL, sfz VARCHAR(50) DEFAULT NULL, zp VARCHAR(100) DEFAULT NULL, sm VARCHAR(2000) DEFAULT NULL, main VARCHAR(20) DEFAULT NULL, kg VARCHAR(100) DEFAULT NULL, dxId BIGINT(20) DEFAULT NULL, fzId BIGINT(20) DEFAULT NULL) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; ALTER TABLE `temp_dx` ADD INDEX idx_bm(`bm`); ALTER TABLE `temp_dx` ADD INDEX idx_class(`class`); ALTER TABLE `temp_dx2` ADD INDEX idx_bm(`bm`); ALTER TABLE `temp_dx2` ADD INDEX idx_class(`class`); INSERT INTO temp_dx(team_id, class, bm, mc, sfz, sm, main, dxId, fzId) SELECT team_id, class, bm, temp_dx_main.mc, bm, sm, 'sys', dxId, fzId FROM temp_dx_main group by temp_dx_main.bm, temp_dx_main.mc; CREATE TEMPORARY TABLE IF NOT EXISTS temp_tel( id BIGINT(20) NOT NULL AUTO_INCREMENT, team_id VARCHAR(50) DEFAULT NULL, dx1 VARCHAR(500) DEFAULT NULL, dx2 VARCHAR(500) DEFAULT NULL, count_num int(11) DEFAULT NULL,sm VARCHAR(500) DEFAULT NULL,sbxq int(11) DEFAULT NULL,sjfx int(11) DEFAULT null, PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; ALTER TABLE `temp_tel` ADD INDEX idx_dx1(`dx1`); ALTER TABLE `temp_tel` ADD INDEX idx_dx2(`dx2`); CREATE TEMPORARY TABLE IF NOT EXISTS temp_yh( id BIGINT(20) NOT NULL AUTO_INCREMENT, team_id VARCHAR(50) DEFAULT NULL, dx1 VARCHAR(500) DEFAULT NULL, dx2 VARCHAR(500) DEFAULT NULL, count_num DECIMAL(38, 2) DEFAULT NULL, sm VARCHAR(200) DEFAULT NULL,sbxq int(11) DEFAULT NULL,sjfx int(11) DEFAULT null, PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; ALTER TABLE `temp_yh` ADD INDEX idx_dx1(`dx1`); ALTER TABLE `temp_yh` ADD INDEX idx_dx2(`dx2`); TRUNCATE TABLE temp_dx2; INSERT INTO temp_dx2(id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId) select id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId from temp_dx; INSERT INTO temp_dx (class, bm, mc) SELECT clazz, obj, mc FROM temp_clean_dx WHERE clazz = 'card' AND obj IN (SELECT obj2 FROM temp_clean_gx as ctg where ctg.clazz in ('sfz_card') AND obj1 IN (SELECT bm FROM temp_dx2)); TRUNCATE TABLE temp_dx2; INSERT INTO temp_dx2(id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId) select id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId from temp_dx; INSERT INTO temp_yh(dx1, dx2, count_num, sm, sbxq, sjfx) SELECT obj1, obj2, num, sm, sbxq, sjfx FROM temp_clean_gx WHERE clazz = 'card_card' and sjfx = 1 AND obj1 IN (SELECT bm FROM temp_dx where bm is not null) AND obj2 NOT IN (SELECT bm FROM temp_dx2 where bm is not null) GROUP BY obj1, obj2, sj; INSERT INTO temp_yh(dx1, dx2, count_num, sm, sbxq, sjfx) SELECT obj1, obj2, num, sm, sbxq, sjfx FROM temp_clean_gx WHERE clazz = 'card_card' and sjfx = 2 AND obj2 IN (SELECT bm FROM temp_dx where bm is not null) AND obj1 NOT IN (SELECT bm FROM temp_dx2 where bm is not null) GROUP BY obj1, obj2, sj; INSERT INTO temp_dx(class, bm, mc) SELECT 'card_gt', obj, mc FROM temp_clean_dx WHERE clazz IN ('card') AND obj IN (SELECT dx2 FROM temp_yh where sjfx = 1 GROUP BY dx2 HAVING count(dx1) > 1); INSERT INTO temp_dx(class, bm, mc) SELECT 'card_gt', obj, mc FROM temp_clean_dx WHERE clazz IN ('card') AND obj IN (SELECT dx1 FROM temp_yh where sjfx = 2 GROUP BY dx1 HAVING count(dx2) > 1); TRUNCATE TABLE temp_dx2; INSERT INTO temp_dx2(id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId) select id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId from temp_dx; truncate temp_yh; TRUNCATE TABLE temp_dx2; INSERT INTO temp_dx2(id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId) select id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId from temp_dx; INSERT INTO temp_yh(dx1, dx2, count_num, sm, sbxq, sjfx) SELECT obj1, obj2, num, sm, sbxq, sjfx FROM temp_clean_gx WHERE clazz = 'card_card' and sjfx = 1 AND obj1 IN (SELECT bm FROM temp_dx where bm is not null) AND obj2 IN (SELECT bm FROM temp_dx2 where bm is not null) GROUP BY obj1, obj2, sj; INSERT INTO temp_yh(dx1, dx2, count_num, sm, sbxq, sjfx) SELECT obj1, obj2, num, sm, sbxq, sjfx FROM temp_clean_gx WHERE clazz = 'card_card' and sjfx = 2 AND obj2 IN (SELECT bm FROM temp_dx where bm is not null) AND obj1 IN (SELECT bm FROM temp_dx2 where bm is not null) GROUP BY obj1, obj2, sj; CREATE TEMPORARY TABLE IF NOT EXISTS temp_gx( id BIGINT(20) NOT NULL AUTO_INCREMENT, clazz VARCHAR(20) DEFAULT NULL, dx1 VARCHAR(500) DEFAULT NULL, dx2 VARCHAR(500) DEFAULT NULL, mc VARCHAR(200) DEFAULT NULL, sm TEXT DEFAULT NULL, team_dx1 VARCHAR(50) DEFAULT NULL, team_dx2 VARCHAR(50) DEFAULT NULL,num VARCHAR(20) DEFAULT NULL,obj1_name VARCHAR(150) DEFAULT NULL, obj2_name VARCHAR(150) DEFAULT NULL, sbxq int(11) DEFAULT NULL,sjfx int(11) DEFAULT NULL, cs int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; ALTER TABLE `temp_gx` ADD INDEX idx_dx1(`dx1`); ALTER TABLE `temp_gx` ADD INDEX idx_dx2(`dx1`); ALTER TABLE `temp_gx` ADD INDEX idx_clazz(`clazz`); TRUNCATE TABLE temp_dx2; INSERT INTO temp_dx2(id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId) select id, class, type, bm, mc, sfz, zp, sm, main, kg, fzId from temp_dx; INSERT INTO temp_gx(clazz, dx1, dx2, mc, sm, num, obj1_name, obj2_name, sbxq, cs, sjfx) SELECT clazz, obj1, obj2, gx_mc, sm, num, obj1_name, obj2_name, sbxq, cs, sjfx FROM temp_clean_gx WHERE obj1 IN (SELECT bm FROM temp_dx) AND obj2 IN (SELECT bm FROM temp_dx2) AND clazz NOT IN ('card_card', 'sfz_sfz', 'sfz_obj', 'tel_tel'); INSERT INTO temp_gx(clazz, dx1, dx2, mc, sm, num, obj1_name, obj2_name, sbxq, cs) SELECT clazz, obj1, obj2, gx_mc, sm, num, obj1_name, obj2_name, sbxq, cs FROM temp_clean_gx WHERE clazz = 'card_card' and obj1 IN (SELECT bm FROM temp_dx) AND obj2 IN (SELECT bm FROM temp_dx2); SELECT *, max(dxId) as dxId0, concat(IFNULL(mc, ''), ' ', bm) as mc1 FROM temp_dx group by bm; SELECT id, clazz, dx1, dx2, mc, sm, obj1_name, obj2_name FROM temp_gx where clazz not in ('card_card', 'tel_tel') group by dx1, dx2; select * from (SELECT id, clazz, dx1, dx2, SUM(cs) AS nums, SUM(num) AS money, sbxq, sjfx FROM temp_gx where clazz = 'card_card' group by dx1, dx2) as asdf where clazz = 'card_card'; SELECT id, clazz, dx1, dx2, SUM(num) as nums, sm, obj1_name, obj2_name, sbxq, sjfx FROM temp_gx where clazz = 'tel_tel' group by dx1, dx2 having nums >= 0;