项目运用情景：新建表需要原表的数据
1. 如果两张张表（导出表和目标表）的字段一致，并且希望插入全部数据，可以用这种方法：
INSERT INTO 目标表 SELECT * FROM 来源表 ;
例如，要将 articles 表插入到 newArticles 表中，则可以通过如下SQL语句实现：
INSERT INTO newArticles SELECT * FROM articles ;
2. 如果只希望导入指定字段，可以用这种方法：
INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表 ; 注意后面是没有括号的
请注意以上两表的字段必须一致，否则会出现数据转换错误。
3.项目运用 ： A为新建档案表， B为原有档案表主表
INSERT INTO A(
pk_sr_main,
xzqh,
mhz,
mhzsfz
) SELECT
pk_sr_main,
xzqh,
mhz,
mhzsfz
FROM
B
WHERE
mhzsfz = '332627196510082957';
------------------------------------------------------------------
INSERT INTO A (
sys_spzt,
sys_djzt,
sys_scbj,
mdjlx,
pk_sr_main
) SELECT
(
CASE
WHEN bzzt = '在保' THEN
1
ELSE
4
END
) AS sys_spzt,
(
CASE
WHEN bzzt = '在保' THEN
1
ELSE
0
END
) AS sys_djzt,
0 AS sys_scbj,
'aa' AS mdjlx,
? AS pk_sr_main
FROM
B
WHERE
pk_sr_main = ? ";
---------------------------------------------------------
INSERT INTO 新建表(
pk_sr_main,
fk_sr_zjff_main,
sys_xzqh,
xzqh,
mhz,
mhzsfz,
BTFFJE
) SELECT
nextval ('id'),
'e1a20dc7-b3e8-4cff-8451-aa372a57300d',
a.sys_xzqh,
a.xzqh,
a.mhz,
a.mhzsfz,
CASE
WHEN b.myhzh IS NOT NULL THEN
b.mkhyh
ELSE
a.mkhyh
END AS mkhyh,
CASE
WHEN b.myhzh IS NOT NULL THEN
b.mkhr
ELSE
a.mkhr
END AS mkhr,
CASE
WHEN b.myhzh IS NOT NULL THEN
b.myhzh
ELSE
a.myhzh
END AS myhzh,
CASE
WHEN b.mjjzbh IS NOT NULL THEN
b.mjjzbh
ELSE
a.mjjzbh
END AS mjjzbh,
'0.0'
FROM
原有表A a
LEFT JOIN 原有表A b ON (
b.sys_xzqh LIKE concat('330101', '%')
AND b.sys_spzt = 1
AND b.sys_djzt = 1
AND b.mdjlx = 'mj_jjzg'
AND b.sys_scbj = 0
AND b.sjbfnf = '2020'
AND b.sjbfyf = '02'
AND a.sjbfyzj = b.sjbfyzj
)
WHERE
a.sys_xzqh LIKE concat('330101', '%')
AND a.sys_spzt = 1
AND a.sys_djzt = 1
AND a.mdjlx = 'mj_jjzg'
AND a.sys_scbj = 0
AND a.sjbfnf = '2020'
AND a.sjbfyf = '02'
------------------------------------------------------------------------------------------------
insert into .. select ...和union all 及group by的结合
String sql = "delete from sjycpc_report_tbwj_bf where date = ?; "
+" insert into sjycpc_report_tbwj_bf (xzqh,node_level,qhmc,zbsl_0,zbsl_1,zbsl_2,zbsl_3,zbsl_4,sqycsl,sqzgsl,sqzgbl,zjffzs,date,qs) "
+" SELECT n.*,? as date,? as qs from ( "
+" select 33 as xzqh,2 as node_level,'全省共计' as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from (select substr(sys_xzqh,1,2) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '33%' ) a "
+" UNION all "
+" select 3301 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3301%' ) a "
+" UNION all"
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3301%' ) a group by xzqh "
+" UNION all "
+" select 3302 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3302%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3302%' ) a group by xzqh "
+" UNION all "
+" select 3303 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3303%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3303%' ) a group by xzqh "
+" UNION all "
+" select 3304 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3304%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3304%' ) a group by xzqh "
+" UNION all "
+" select 3305 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3305%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl ,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3305%' ) a group by xzqh "
+" UNION all "
+" select 3306 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3306%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3306%' ) a group by xzqh "
+" UNION all "
+" select 3307 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3307%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3307%' ) a group by xzqh "
+" UNION all "
+" select 3308 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl ,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3308%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3308%' ) a group by xzqh "
+" UNION all "
+" select 3309 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3309%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3309%' ) a group by xzqh "
+" UNION all "
+" select 3310 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3310%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3310%' ) a group by xzqh "
+" UNION all "
+" select 3311 as xzqh,4 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,4) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3311%' ) a "
+" UNION all "
+" select xzqh,6 as node_level,(select xjgmc from mz_xzjg where node_id = a.xzqh) as qhmc,sum(zbsl_0) as zbsl_0,sum(zbsl_1) as zbsl_1,sum(zbsl_2) as zbsl_2,sum(zbsl_3) as zbsl_3,sum(zbsl_4) as zbsl_4,sum(sqycsl) AS sqycsl,sum(sqzgsl) AS sqzgsl,(sum(sqzgsl) / sum(sqycsl)) * 100 AS sqzgbl,(select count(1)*3 from mz_xzjg where node_level = 3 and sfjjkfq = 1 and xdel = 0 and node_id like concat(a.xzqh,'%')) as zjffzs from ( select substr(sys_xzqh,1,6) as xzqh,CASE WHEN (sys_clbj = 1 OR bczt = 1) THEN 1 else 0 END AS sqzgsl,case when (sys_clbj = 1 OR bczt = 1 OR sys_clbj = 0 OR bczt = 0 )THEN 1 else 0 END AS sqycsl,case when type = 0 then 1 else 0 end as zbsl_0,case when type = 1 then 1 else 0 end as zbsl_1,case when type = 2 then 1 else 0 end as zbsl_2,case when type = 3 then 1 else 0 end as zbsl_3,case when type = 4 then 1 else 0 end as zbsl_4 from sr_sjycpc WHERE sys_xzqh like '3311%' ) a group by xzqh "
+" ) n ";