select
A.g0company_code AS g0company_code,
A.K0YEAR AS K0YEAR,
A.G0field_val as 硕士及以上,
B.G0field_val as 本科,
C.G0field_val as 专科,
D.G0field_val as 中专及以下
from
(
SELECT
tb3_2.g0company_code,tb3_2.K0YEAR,tb3_1.G0field_val
FROM
(
select *
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.a2cw1012 as A
where K0YEAR >= 2018
and K0YEAR <= 2020
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
)tb3_1
JOIN
(
select k0column_id + 2 AS k0column_id ,k0row_id,K0YEAR,g0company_code
from a2cw1012
where G0field_val like '%硕士及以上%'
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
and K0YEAR >= 2018
and K0YEAR <= 2020
) tb3_2
ON tb3_1.k0column_id = tb3_2.k0column_id
AND tb3_1.k0row_id = tb3_2.k0row_id
AND tb3_1.K0YEAR = tb3_2.K0YEAR
AND tb3_1.g0company_code = tb3_2.g0company_code)A
JOIN
(
SELECT
tb3_2.g0company_code,tb3_2.K0YEAR,tb3_1.G0field_val
FROM
(
select *
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.a2cw1012 as A
where K0YEAR >= 2018
and K0YEAR <= 2020
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
)tb3_1
JOIN
(
select k0column_id + 2 AS k0column_id ,k0row_id,K0YEAR,g0company_code
from a2cw1012
where G0field_val like '%本科%'
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
and K0YEAR >= 2018
and K0YEAR <= 2020
) tb3_2
ON tb3_1.k0column_id = tb3_2.k0column_id
AND tb3_1.k0row_id = tb3_2.k0row_id
AND tb3_1.K0YEAR = tb3_2.K0YEAR
AND tb3_1.g0company_code = tb3_2.g0company_code
)B
ON A.g0company_code = B.g0company_code
AND A.K0YEAR = B.K0YEAR
join
(
SELECT
tb3_2.g0company_code,tb3_2.K0YEAR,tb3_1.G0field_val
FROM
(
select *
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.a2cw1012 as A
where K0YEAR >= 2018
and K0YEAR <= 2020
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
)tb3_1
JOIN
(
select k0column_id + 2 AS k0column_id ,k0row_id,K0YEAR,g0company_code
from a2cw1012
where G0field_val like '%专科%'
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
and K0YEAR >= 2018
and K0YEAR <= 2020
) tb3_2
ON tb3_1.k0column_id = tb3_2.k0column_id
AND tb3_1.k0row_id = tb3_2.k0row_id
AND tb3_1.K0YEAR = tb3_2.K0YEAR
AND tb3_1.g0company_code = tb3_2.g0company_code
) C
ON A.g0company_code = C.g0company_code
AND A.K0YEAR = C.K0YEAR
join
(
SELECT
tb3_2.g0company_code,tb3_2.K0YEAR,tb3_1.G0field_val
FROM
(
select *
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.a2cw1012 as A
where K0YEAR >= 2018
and K0YEAR <= 2020
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
)tb3_1
JOIN
(
select k0column_id + 2 AS k0column_id ,k0row_id,K0YEAR,g0company_code
from a2cw1012
where G0field_val like '%中专及以下%'
and G0tab_name like '员工情况%'
and g0company_code IN (select ZGXTQYBM
from analytic_layer_zbyy_sjbyy_002_ysrhdsj00.ZDEDW_ZZJGML_HX
where ztyqybm IN ('7274012948')
and zlogsys2 = 'QYNB')
and K0YEAR >= 2018
and K0YEAR <= 2020
) tb3_2
ON tb3_1.k0column_id = tb3_2.k0column_id
AND tb3_1.k0row_id = tb3_2.k0row_id
AND tb3_1.K0YEAR = tb3_2.K0YEAR
AND tb3_1.g0company_code = tb3_2.g0company_code
) D
ON A.g0company_code = D.g0company_code
AND A.K0YEAR = D.K0YEAR
7.67s