zsjz-ai/sql/testSql/交易共同.sql

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;