MySQL | MySQL更改系统部门的执法主体与所属区域



分层级是初步摸索的sql语句，整体是对于分层sql的总结与封装

分层级更新

层级说明：

  • 衡阳市
    • 衡阳市直各部门-衡阳市水利局-局安全信息科
    • 衡山县-衡山县直各部门-衡山县统计局

对市级的下下级进行改动

  1. 对市下下级部门region_id的改动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 查找senior_id为1的各部门的unit_id和region_id
SELECT unit_id,unit_name from tc_unit where  senior_id = 1 AND delete_flag != 1 

-- 筛选出senior_id为37（衡阳市）的下下级
SELECT  unit_id,unit_name,region_id 
FROM    tc_unit a
WHERE   a.senior_id 
IN (SELECT unit_id 
trueFROM   (SELECT a.unit_id
truetruetrueFROM tc_unit a
truetruetrueWHERE a.senior_id = 37 
truetruetrueAND a.delete_flag != 1 ) t)

-- 更新语句 1.123s
UPDATE  tc_unit a 
set     region_id = 5 
WHERE   a.senior_id 
IN (SELECT unit_id 
trueFROM   (SELECT a.unit_id
truetruetrueFROM tc_unit a
truetruetrueWHERE a.senior_id = 37 
truetruetrueAND a.delete_flag != 1 ) t)

-- 优化更新语句 
UPDATE  tc_unit a
JOIN   (SELECT a.unit_id
truetrueFROM tc_unit a 
truetrueWHERE a.senior_id = 37 
truetrueAND a.delete_flag != 1) t
ON a.senior_id = t.unit_id
SET     region_id = 5
  1. 对市下下级部门law_subject_id的改动
1
2
3
4
5
6
7
UPDATE tc_unit a 
set law_subject_id = 
WHERE a.senior_id 
IN (SELECT unit_id 
trueFROM tc_unit 
trueWHERE senior_id = 37 
trueAND delete_flag != 1 )

对市级的下下下级进行改动

  1. 对市下下下级部门region_id的改动
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    -- 筛选出senior_id为37（衡阳市）的下下下级
    SELECT  unit_id,unit_name,region_id 
    FROM    tc_unit a
    WHERE   a.senior_id 
    IN (SELECT unit_id
    trueFROM   (SELECT a.unit_id 
    truetruetrueFROM tc_unit a 
    truetruetrueWHERE a.senior_id 
    truetruetrueIN (SELECT b.unit_id
    truetruetruetrueFROM tc_unit b
    truetruetruetrueWHERE b.senior_id = 37 
    truetruetruetrueAND b.delete_flag != 1 ) ) y)

    -- -- 更新语句 1.285s
    UPDATE  tc_unit a 
    set 	region_id = 5 
    WHERE 	a.senior_id 
    IN (SELECT unit_id
    trueFROM   (SELECT a.unit_id 
    truetruetrueFROM tc_unit a 
    truetruetrueWHERE a.senior_id 
    truetruetrueIN (SELECT b.unit_id
    truetruetruetrueFROM tc_unit b
    truetruetruetrueWHERE b.senior_id = 37 
    truetruetruetrueAND b.delete_flag != 1 ) ) y)

整体层级更新

  1. 所属区域的改动

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    -- 查询
    SELECT unit_id,unit_name,region_id 
    FROM tc_unit a
    WHERE a.senior_id = 37 
    AND a.delete_flag != 1 
    OR	  a.senior_id 
    IN (SELECT unit_id 
    trueFROM   (SELECT a.unit_id
    truetruetrueFROM tc_unit a
    truetruetrueWHERE a.senior_id = 37 
    truetruetrueAND a.delete_flag != 1 ) t)
    OR	  a.senior_id 
    IN (SELECT unit_id
    trueFROM   (SELECT a.unit_id 
    truetruetrueFROM tc_unit a 
    truetruetrueWHERE a.senior_id 
    truetruetrueIN (SELECT b.unit_id
    truetruetruetrueFROM tc_unit b
    truetruetruetrueWHERE b.senior_id = 37 
    truetruetruetrueAND b.delete_flag != 1 ) ) y)

    -- 更新
    UPDATE  tc_unit a 
    set 	region_id = 5 
    WHERE a.senior_id = 37 
    AND a.delete_flag != 1 
    OR	  a.senior_id 
    IN (SELECT unit_id 
    trueFROM   (SELECT a.unit_id
    truetruetrueFROM tc_unit a
    truetruetrueWHERE a.senior_id = 37 
    truetruetrueAND a.delete_flag != 1 ) t)
    OR	  a.senior_id 
    IN (SELECT unit_id
    trueFROM   (SELECT a.unit_id 
    truetruetrueFROM tc_unit a 
    truetruetrueWHERE a.senior_id 
    truetruetrueIN (SELECT b.unit_id
    truetruetruetrueFROM tc_unit b
    truetruetruetrueWHERE b.senior_id = 37 
    truetruetruetrueAND b.delete_flag != 1 ) ) y)

  2. 执法主体的改动

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    -- 查询
    SELECT unit_name,region_id,unit_id,law_subject_id
    FROM tc_unit a
    WHERE a.senior_id = 1 
    AND a.delete_flag != 1 
    OR	  a.senior_id 
    IN (SELECT unit_id 
    trueFROM   (SELECT a.unit_id
    truetruetrueFROM tc_unit a
    truetruetrueWHERE a.senior_id = 1 
    truetruetrueAND a.delete_flag != 1 ) t)
    OR	  a.senior_id 
    IN (SELECT unit_id
    trueFROM   (SELECT a.unit_id 
    truetruetrueFROM tc_unit a 
    truetruetrueWHERE a.senior_id 
    truetruetrueIN (SELECT b.unit_id
    truetruetruetrueFROM tc_unit b
    truetruetruetrueWHERE b.senior_id = 1 
    truetruetruetrueAND b.delete_flag != 1 ) ) y)
    truetruetruetrue
    -- 更新
    UPDATE  tc_unit a 
    set law_subject_id = unit_id
    WHERE a.senior_id = 1 
    AND a.delete_flag != 1 
    OR	  a.senior_id 
    IN (SELECT unit_id 
    trueFROM   (SELECT a.unit_id
    truetruetrueFROM tc_unit a
    truetruetrueWHERE a.senior_id = 1 
    truetruetrueAND a.delete_flag != 1 ) t)
    OR	  a.senior_id 
    IN (SELECT unit_id
    trueFROM   (SELECT a.unit_id 
    truetruetrueFROM tc_unit a 
    truetruetrueWHERE a.senior_id 
    truetruetrueIN (SELECT b.unit_id
    truetruetruetrueFROM tc_unit b
    truetruetruetrueWHERE b.senior_id = 1 
    truetruetruetrueAND b.delete_flag != 1 ) ) y)