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', '513126199410052413', '成都市章大妈农副产品有限公司', '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 = 'tel'
  AND obj IN (SELECT obj2 FROM temp_clean_gx WHERE clazz IN ('sfz_tel') 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_tel(dx1, team_id, dx2, count_num, sm, sbxq, sjfx)
SELECT obj1, obj1 AS team_id, obj2, num, sm, sbxq, sjfx
FROM temp_clean_gx
WHERE clazz = 'tel_tel'
  AND obj1 IN (SELECT bm FROM temp_dx)
  AND obj2 IN (SELECT bm FROM temp_dx2)
GROUP BY obj1, obj2;


INSERT INTO temp_tel(dx1, team_id, dx2, count_num, sm, sbxq, sjfx)
SELECT obj1, obj1 AS team_id, obj2, num, sm, sbxq, sjfx
FROM temp_clean_gx
WHERE clazz = 'tel_tel'
  and sjfx = 2
  AND obj2 IN (SELECT bm FROM temp_dx)
  AND obj1 IN (SELECT bm FROM temp_dx2)
GROUP BY obj1, obj2;


INSERT INTO temp_dx(class, bm, mc)
SELECT 'tel_gt', obj, mc
FROM temp_clean_dx
WHERE temp_clean_dx.clazz IN ('tel')
  AND obj IN (SELECT dx2 FROM temp_tel WHERE cast(IFNULL(count_num, 0) AS UNSIGNED) >= 1 GROUP BY 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;
TRUNCATE TABLE temp_tel;
INSERT INTO temp_tel(dx1, dx2, count_num, sm, sbxq, sjfx)
SELECT obj1, obj2, num, sm, sbxq, sjfx
FROM temp_clean_gx
WHERE clazz = 'tel_tel'
  AND obj1 IN (SELECT bm FROM temp_dx)
  AND obj2 IN (SELECT bm FROM temp_dx2)
GROUP BY obj1, obj2;
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');
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'
  AND cast(IFNULL(money, 0) as decimal(38, 2)) >= 1;
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 >= 1;