SQL优化记录

system>const>eq_ref>ref>range>index>all （观察影响的row数量）

SELECT ltg.class_name as type, atg.class_name, count( 1 ) as group_count
FROM (
SELECT TIMESTAMPDIFF(month, p.START_DATE, p.end_date) AS age,
d.id
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
where p.END_DATE >=‘2016-08-31’
and p.END_DATE <= ‘2022-08-31’

and d.id = p.id
and d.id in
(
select distinct r.PERSONAL_COde from ams_personal_relation r
INNER JOIN ams_organization o
ON r.ORG_BRNCH_CODE =o.org_brnch_code and o.rec_flag =1
INNER JOIN ams_position o1
ON r.position_code =o1.position_code and o1.rec_flag=1

)

) t
inner join ams_personal_relation r on t.id = r.id
inner JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’
inner JOIN statistics ltg
ON ltg.class_value = r.POSITION_CODE AND ltg.template_id = ‘T003’
GROUP BY atg.group_id,ltg.group_id;

---

SELECT ltg.class_name as type, atg.class_name, count( 1 ) as group_count
FROM (
SELECT TIMESTAMPDIFF(month, p.START_DATE, p.end_date) AS age,p.id
FROM ams_personal_info p,ams_personal_info_detail d,(
select distinct PERSONAL_CODE from ams_personal_relation r
INNER JOIN ams_organization o
ON r.ORG_BRNCH_CODE =o.org_brnch_code and o.rec_flag =1
INNER JOIN ams_position o1
ON r.position_code =o1.position_code and o1.rec_flag=1
) x
where p.END_DATE >=‘2016-08-31’
and p.END_DATE <= ‘2022-08-31’
and d.id = p.id
and d.id = x.PERSONAL_CODE

) t
inner join ams_personal_relation r on t.id = r.id
inner JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’
inner JOIN statistics ltg
ON ltg.class_value = r.POSITION_CODE AND ltg.template_id = ‘T003’
GROUP BY atg.group_id,ltg.group_id;

---

SELECT
atg.class_name as type, atg.subclass_name, if(count( 1 ) != 0,count( 1 ),0) as group_count
FROM
(
SELECT
TIMESTAMPDIFF(
month,
p.START_DATE,
‘2022-06-06’) AS age,
d.EMPLOYEE_POSITION_LEVEL,
d.ID
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
where
p.START_DATE<=‘2022-06-06’
and p.END_DATE >‘2022-06-06’
and d.id in (
select distinct r.PERSONAL_CODE from ams_personal_relation r
INNER JOIN ams_organization o
ON r.ORG_BRNCH_CODE =o.org_brnch_code
INNER JOIN ams_position o1
ON r.position_code =o1.position_code
and o.END_VALID_TIME>‘2022-06-06’ and ‘2022-06-06’>o.BGN_VALID_TIME
and r.END_VALID_TIME>‘2022-06-06’ and ‘2022-06-06’>r.BGN_VALID_TIME
and o1.END_DATE>‘2022-06-06’ and ‘2022-06-06’>o1.START_DATE

    )) t
    INNER JOIN statistics atg ON t.EMPLOYEE_POSITION_LEVEL = atg.class_value
1
2

where atg.template_id = ‘T006’
GROUP BY
atg.group_id,atg.subgroup_id;

---

SELECT ltg.class_name as type, atg.class_name, count( 1 ) as group_count
FROM (
SELECT TIMESTAMPDIFF(month, p.START_DATE, p.end_date) AS age,x.position_code
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id and p.BL_USE=‘0’ and p.ps_emp_no is not null and p.END_DATE =]]> #{ofWorkingHoursServiceDTO.timeStart}
and p.END_DATE #{ofWorkingHoursServiceDTO.endStart}

inner join
(
select ams_personal_relation.PERSONAL_CODE,ams_personal_relation.position_code from
(SELECT distinct r.PERSONAL_CODE
FROM ams_personal_relation r
INNER JOIN ams_organization o
ON r.ORG_BRNCH_CODE = o.org_brnch_code
INNER JOIN ams_position o1
ON r.position_code = o1.position_code order by r.END_VALID_TIME desc)
z inner join ams_personal_relation
on ams_personal_relation.PERSONAL_CODE = z.PERSONAL_CODE
group by z.PERSONAL_CODE
) x
on p.id = x.PERSONAL_CODE

)t
inner JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’
inner JOIN statistics ltg
ON ltg.class_value = t.position_code AND ltg.template_id = ‘T003’ GROUP BY atg.group_id,ltg.group_id;

---

任职时长统计(在职)：按部门统计所有

SELECT
atg.class_name,
count(1) AS group_count
FROM
(
SELECT
TIMESTAMPDIFF(
MONTH,
p.START_DATE,
‘2022-09-16’
) AS age
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
INNER JOIN ams_personal_relation r ON r.PERSONAL_CODE = p.id
AND ‘2022-09-16’ >= r.BGN_VALID_TIME
AND ‘2022-09-16’ <= r.END_VALID_TIME
INNER JOIN ams_organization o ON o.org_brnch_code = r.ORG_BRNCH_CODE
AND o.rec_flag = ‘1’
WHERE
p.REC_FLAG = ‘1’
AND p.BL_USE != ‘0’
AND p.PS_EMP_NO IS NOT NULL
AND p.PERSONAL_NAME NOT LIKE ‘%测试%’
AND (
p.ID_CARD IS NOT NULL
AND p.ID_CARD != ‘’
)
AND ‘2022-09-16’ >= p.START_DATE
AND ‘2022-09-16’ <= p.END_DATE
) t
LEFT JOIN statistics atg ON atg.class_value = t.age
AND atg.template_id = ‘T01’
GROUP BY
atg.group_id;

任职时长统计(在职)：按部门统计所有 (中通总部）

SELECT
atg.class_name,
count(1) AS group_count
FROM
(
SELECT
TIMESTAMPDIFF(
MONTH,
p.START_DATE,
‘2022-09-16’
) AS age
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
INNER JOIN ams_personal_relation r ON r.PERSONAL_CODE = p.id
AND ‘2022-09-16’ >= r.BGN_VALID_TIME
AND ‘2022-09-16’ <= r.END_VALID_TIME
INNER JOIN ams_organization o ON o.org_brnch_code = r.ORG_BRNCH_CODE
AND o.rec_flag = ‘1’
WHERE
p.REC_FLAG = ‘1’
AND p.BL_USE != ‘0’
AND p.PS_EMP_NO IS NOT NULL
AND p.PERSONAL_NAME NOT LIKE ‘%测试%’
AND (
p.ID_CARD IS NOT NULL
AND p.ID_CARD != ‘’
)
AND ‘2022-09-16’ >= p.START_DATE
AND ‘2022-09-16’ <= p.END_DATE
AND( o.org_brnch_uniq_path_name LIKE ‘/总裁办%’
OR o.org_brnch_uniq_path_name LIKE ‘/网络管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/市场营销中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/转运管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/服务质量中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/财务管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/人力资源中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/IT信息中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/安全监察中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/公共事务管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/营运办%’
OR o.org_brnch_uniq_path_name LIKE ‘/陆运管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/综合物流中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/资产管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/资产管理中心/采购部%’
OR o.org_brnch_uniq_path_name LIKE ‘/行政部%’
OR o.org_brnch_uniq_path_name LIKE ‘/审计部%’)
) t
LEFT JOIN statistics atg ON atg.class_value = t.age
AND atg.template_id = ‘T01’
GROUP BY
atg.group_id;

任职时长统计(在职)：按部门统计所有 (省区汇总）

SELECT
atg.class_name,
count(1) AS group_count
FROM
(
SELECT
TIMESTAMPDIFF(
MONTH,
p.START_DATE,
‘2022-09-16’
) AS age
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
INNER JOIN ams_personal_relation r ON r.PERSONAL_CODE = p.id
AND ‘2022-09-16’ >= r.BGN_VALID_TIME
AND ‘2022-09-16’ <= r.END_VALID_TIME
INNER JOIN ams_organization o ON o.org_brnch_code = r.ORG_BRNCH_CODE
AND o.rec_flag = ‘1’
WHERE
p.REC_FLAG = ‘1’
AND p.BL_USE != ‘0’
AND p.PS_EMP_NO IS NOT NULL
AND p.PERSONAL_NAME NOT LIKE ‘%测试%’
AND (
p.ID_CARD IS NOT NULL
AND p.ID_CARD != ‘’
)
AND ‘2022-09-16’ >= p.START_DATE
AND ‘2022-09-16’ <= p.END_DATE
AND ( o.org_brnch_uniq_path_name LIKE ‘/上海公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/福建省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/北京公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/河北省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/山西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/江西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/河南省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/湖北省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/湖南省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/四川省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/贵州省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/云南省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/重庆公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/广西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/黑龙江省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/吉林省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/辽宁省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/陕西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/新疆公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/甘肃省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/浙江省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/江苏省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/山东省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/安徽省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/广东省公司%’)
) t
LEFT JOIN statistics atg ON atg.class_value = t.age
AND atg.template_id = ‘T01’
GROUP BY
atg.group_id;

#-----------------------------------------------------------------------------------------------------------------

任职时长统计(在职)：按岗位职级统计所有

SELECT
ltg.class_name AS type,
atg.class_name,
count(1) AS group_count
FROM
(
SELECT
TIMESTAMPDIFF(
MONTH,
p.START_DATE,
‘2022-09-16’
) AS age,
d.EMPLOYEE_POSITION_LEVEL
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
INNER JOIN ams_personal_relation r ON r.PERSONAL_CODE = p.id
AND ‘2022-09-16’ >= r.BGN_VALID_TIME
AND ‘2022-09-16’ <= r.END_VALID_TIME
INNER JOIN ams_organization o ON o.org_brnch_code = r.ORG_BRNCH_CODE
AND o.rec_flag = ‘1’
INNER JOIN ams_position o1 ON r.position_code = o1.position_code
AND ‘2022-09-16’ >= o1.START_DATE
AND ‘2022-09-16’ <= o1.END_DATE
AND o1.rec_flag = ‘1’
WHERE
p.REC_FLAG = ‘1’
AND p.BL_USE != ‘0’
AND p.PS_EMP_NO IS NOT NULL
AND p.PERSONAL_NAME NOT LIKE ‘%测试%’
AND (
p.ID_CARD IS NOT NULL
AND p.ID_CARD != ‘’
)
AND ‘2022-09-16’ >= p.START_DATE
AND ‘2022-09-16’ <= p.END_DATE
) t
INNER JOIN statistics atg ON atg.class_value = t.age
AND atg.template_id = ‘T01’
INNER JOIN statistics ltg ON ltg.class_value = t.EMPLOYEE_POSITION_LEVEL
AND ltg.template_id = ‘T002’
GROUP BY
atg.group_id,
ltg.group_id;

任职时长统计(在职)：按岗位职级统计所有(中通总部）

SELECT
ltg.class_name AS type,
atg.class_name,
count(1) AS group_count
FROM
(
SELECT
TIMESTAMPDIFF(
MONTH,
p.START_DATE,
‘2022-09-16’
) AS age,
d.EMPLOYEE_POSITION_LEVEL
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
INNER JOIN ams_personal_relation r ON r.PERSONAL_CODE = p.id
AND ‘2022-09-16’ >= r.BGN_VALID_TIME
AND ‘2022-09-16’ <= r.END_VALID_TIME
INNER JOIN ams_organization o ON o.org_brnch_code = r.ORG_BRNCH_CODE
AND o.rec_flag = ‘1’
INNER JOIN ams_position o1 ON r.position_code = o1.position_code
AND ‘2022-09-16’ >= o1.START_DATE
AND ‘2022-09-16’ <= o1.END_DATE
AND o1.rec_flag = ‘1’
WHERE
p.REC_FLAG = ‘1’
AND p.BL_USE != ‘0’
AND p.PS_EMP_NO IS NOT NULL
AND p.PERSONAL_NAME NOT LIKE ‘%测试%’
AND (
p.ID_CARD IS NOT NULL
AND p.ID_CARD != ‘’
)
AND ‘2022-09-16’ >= p.START_DATE
AND ‘2022-09-16’ <= p.END_DATE
AND ( o.org_brnch_uniq_path_name LIKE ‘/总裁办%’
OR o.org_brnch_uniq_path_name LIKE ‘/网络管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/市场营销中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/转运管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/服务质量中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/财务管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/人力资源中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/IT信息中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/安全监察中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/公共事务管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/营运办%’
OR o.org_brnch_uniq_path_name LIKE ‘/陆运管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/综合物流中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/资产管理中心%’
OR o.org_brnch_uniq_path_name LIKE ‘/资产管理中心/采购部%’
OR o.org_brnch_uniq_path_name LIKE ‘/行政部%’
OR o.org_brnch_uniq_path_name LIKE ‘/审计部%’)
) t
INNER JOIN statistics atg ON atg.class_value = t.age
AND atg.template_id = ‘T01’
INNER JOIN statistics ltg ON ltg.class_value = t.EMPLOYEE_POSITION_LEVEL
AND ltg.template_id = ‘T002’
GROUP BY
atg.group_id,
ltg.group_id;

任职时长统计(在职)：按岗位职级统计所有(省区汇总）

SELECT
ltg.class_name AS type,
atg.class_name,
count(1) AS group_count
FROM
(
SELECT
TIMESTAMPDIFF(
MONTH,
p.START_DATE,
‘2022-09-16’
) AS age,
d.EMPLOYEE_POSITION_LEVEL
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
INNER JOIN ams_personal_relation r ON r.PERSONAL_CODE = p.id
AND ‘2022-09-16’ >= r.BGN_VALID_TIME
AND ‘2022-09-16’ <= r.END_VALID_TIME
INNER JOIN ams_organization o ON o.org_brnch_code = r.ORG_BRNCH_CODE
AND o.rec_flag = ‘1’
INNER JOIN ams_position o1 ON r.position_code = o1.position_code
AND ‘2022-09-16’ >= o1.START_DATE
AND ‘2022-09-16’ <= o1.END_DATE
AND o1.rec_flag = ‘1’
WHERE
p.REC_FLAG = ‘1’
AND p.BL_USE != ‘0’
AND p.PS_EMP_NO IS NOT NULL
AND p.PERSONAL_NAME NOT LIKE ‘%测试%’
AND (
p.ID_CARD IS NOT NULL
AND p.ID_CARD != ‘’
)
AND ‘2022-09-16’ >= p.START_DATE
AND ‘2022-09-16’ <= p.END_DATE
AND ( o.org_brnch_uniq_path_name LIKE ‘/上海公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/福建省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/北京公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/河北省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/山西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/江西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/河南省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/湖北省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/湖南省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/四川省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/贵州省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/云南省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/重庆公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/广西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/黑龙江省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/吉林省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/辽宁省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/陕西省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/新疆公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/甘肃省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/浙江省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/江苏省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/山东省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/安徽省公司%’
OR o.org_brnch_uniq_path_name LIKE ‘/广东省公司%’)
) t
INNER JOIN statistics atg ON atg.class_value = t.age
AND atg.template_id = ‘T01’
INNER JOIN statistics ltg ON ltg.class_value = t.EMPLOYEE_POSITION_LEVEL
AND ltg.template_id = ‘T002’
GROUP BY
atg.group_id,
ltg.group_id;

#-----------------------------------------------------------------------------------------------------------------

任职时长统计(在职)：按省区员工统计所有

SELECT
ltg.class_name AS type,
atg.class_name,
count(1) AS group_count
FROM
(
SELECT
TIMESTAMPDIFF(
MONTH,
p.START_DATE,
‘2022-09-16’
) AS age,
r.position_code
FROM
ams_personal_info p
INNER JOIN ams_personal_info_detail d ON d.id = p.id
INNER JOIN ams_personal_relation r ON r.PERSONAL_CODE = p.id
AND ‘2022-09-16’ >= r.BGN_VALID_TIME
AND ‘2022-09-16’ <= r.END_VALID_TIME
INNER JOIN ams_organization o ON o.org_brnch_code = r.ORG_BRNCH_CODE
AND o.rec_flag = ‘1’
INNER JOIN ams_position o1 ON r.position_code = o1.position_code
AND ‘2022-09-16’ >= o1.START_DATE
AND ‘2022-09-16’ <= o1.END_DATE
AND o1.rec_flag = ‘1’
WHERE
p.REC_FLAG = ‘1’
AND p.BL_USE != ‘0’
AND p.PS_EMP_NO IS NOT NULL
AND p.PERSONAL_NAME NOT LIKE ‘%测试%’
AND (
p.ID_CARD IS NOT NULL
AND p.ID_CARD != ‘’
)
AND ‘2022-09-16’ >= p.START_DATE
AND ‘2022-09-16’ <= p.END_DATE
) t
INNER JOIN statistics atg ON atg.class_value = t.age
AND atg.template_id = ‘T01’
INNER JOIN statistics ltg ON ltg.class_value = t.position_code
AND ltg.template_id = ‘T003’
GROUP BY
atg.group_id,
ltg.group_id;

#-----------------------------------------------------------------------------------------------------------------

(时间段区间内）任职时长统计(离职)：按部门统计所有

SELECT atg.class_name, count(1) AS group_count
FROM (
SELECT TIMESTAMPDIFF( MONTH, p.START_DATE, p.end_date ) AS age
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id
INNER JOIN ams_personal_relation r
ON r.PERSONAL_CODE = p.id
INNER JOIN ams_organization o
ON o.org_brnch_code = r.ORG_BRNCH_CODE
WHERE p.BL_USE = ‘0’ AND p.PS_EMP_NO IS NOT NULL AND p.PERSONAL_NAME NOT LIKE ‘%测试%’ AND ( p.ID_CARD IS NOT NULL AND p.ID_CARD != ‘’ ) AND p.END_DATE >= ‘2022-08-01’ AND p.END_DATE <= ‘2022-08-31’ AND r.BGN_VALID_TIME <= p.END_DATE AND p.END_DATE <= r.END_VALID_TIME AND o.bgn_valid_time <= p.END_DATE AND p.END_DATE <= o.end_valid_time ) t
LEFT JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’ GROUP BY atg.group_id;

(时间段区间内）任职时长统计(离职)：按部门统计所有(中通总部）

SELECT atg.class_name, count(1) AS group_count
FROM (
SELECT TIMESTAMPDIFF( MONTH, p.START_DATE, p.end_date ) AS age
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id
INNER JOIN ams_personal_relation r
ON r.PERSONAL_CODE = p.id
INNER JOIN ams_organization o
ON o.org_brnch_code = r.ORG_BRNCH_CODE
WHERE p.BL_USE = ‘0’ AND p.PS_EMP_NO IS NOT NULL AND p.PERSONAL_NAME NOT LIKE ‘%测试%’ AND ( p.ID_CARD IS NOT NULL AND p.ID_CARD != ‘’ ) AND p.END_DATE >= ‘2022-08-01’ AND p.END_DATE <= ‘2022-08-31’ AND r.BGN_VALID_TIME <= p.END_DATE AND p.END_DATE <= r.END_VALID_TIME AND o.bgn_valid_time <= p.END_DATE AND p.END_DATE <= o.end_valid_time and (o.org_brnch_uniq_path_name like ‘/总裁办%’ or o.org_brnch_uniq_path_name like ‘/网络管理中心%’ or o.org_brnch_uniq_path_name like ‘/市场营销中心%’ or o.org_brnch_uniq_path_name like ‘/转运管理中心%’ or o.org_brnch_uniq_path_name like ‘/服务质量中心%’ or o.org_brnch_uniq_path_name like ‘/财务管理中心%’ or o.org_brnch_uniq_path_name like ‘/人力资源中心%’ or o.org_brnch_uniq_path_name like ‘/IT信息中心%’ or o.org_brnch_uniq_path_name like ‘/安全监察中心%’ or o.org_brnch_uniq_path_name like ‘/公共事务管理中心%’ or o.org_brnch_uniq_path_name like ‘/营运办%’ or o.org_brnch_uniq_path_name like ‘/陆运管理中心%’ or o.org_brnch_uniq_path_name like ‘/综合物流中心%’ or o.org_brnch_uniq_path_name like ‘/资产管理中心%’ or o.org_brnch_uniq_path_name like ‘/资产管理中心/采购部%’ or o.org_brnch_uniq_path_name like ‘/行政部%’ or o.org_brnch_uniq_path_name like ‘/审计部%’ ) ) t
LEFT JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’ GROUP BY atg.group_id;

(时间段区间内）任职时长统计(离职)：按部门统计所有(省区汇总）

SELECT atg.class_name, count(1) AS group_count
FROM (
SELECT TIMESTAMPDIFF( MONTH, p.START_DATE, p.end_date ) AS age
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id
INNER JOIN ams_personal_relation r
ON r.PERSONAL_CODE = p.id
INNER JOIN ams_organization o
ON o.org_brnch_code = r.ORG_BRNCH_CODE
WHERE p.BL_USE = ‘0’ AND p.PS_EMP_NO IS NOT NULL AND p.PERSONAL_NAME NOT LIKE ‘%测试%’ AND ( p.ID_CARD IS NOT NULL AND p.ID_CARD != ‘’ ) AND p.END_DATE >= ‘2022-08-01’ AND p.END_DATE <= ‘2022-08-31’ AND r.BGN_VALID_TIME <= p.END_DATE AND p.END_DATE <= r.END_VALID_TIME AND o.bgn_valid_time <= p.END_DATE AND p.END_DATE <= o.end_valid_time and (o.org_brnch_uniq_path_name like ‘/上海公司%’ or o.org_brnch_uniq_path_name like ‘/福建省公司%’ or o.org_brnch_uniq_path_name like ‘/北京公司%’ or o.org_brnch_uniq_path_name like ‘/河北省公司%’ or o.org_brnch_uniq_path_name like ‘/山西省公司%’ or o.org_brnch_uniq_path_name like ‘/江西省公司%’ or o.org_brnch_uniq_path_name like ‘/河南省公司%’ or o.org_brnch_uniq_path_name like ‘/湖北省公司%’ or o.org_brnch_uniq_path_name like ‘/湖南省公司%’ or o.org_brnch_uniq_path_name like ‘/四川省公司%’ or o.org_brnch_uniq_path_name like ‘/贵州省公司%’ or o.org_brnch_uniq_path_name like ‘/云南省公司%’ or o.org_brnch_uniq_path_name like ‘/重庆公司%’ or o.org_brnch_uniq_path_name like ‘/广西省公司%’ or o.org_brnch_uniq_path_name like ‘/黑龙江省公司%’ or o.org_brnch_uniq_path_name like ‘/吉林省公司%’ or o.org_brnch_uniq_path_name like ‘/辽宁省公司%’ or o.org_brnch_uniq_path_name like ‘/陕西省公司%’ or o.org_brnch_uniq_path_name like ‘/新疆公司%’ or o.org_brnch_uniq_path_name like ‘/甘肃省公司%’ or o.org_brnch_uniq_path_name like ‘/浙江省公司%’ or o.org_brnch_uniq_path_name like ‘/江苏省公司%’ or o.org_brnch_uniq_path_name like ‘/山东省公司%’ or o.org_brnch_uniq_path_name like ‘/安徽省公司%’ or o.org_brnch_uniq_path_name like ‘/广东省公司%’ ) ) t
LEFT JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’ GROUP BY atg.group_id;

#-----------------------------------------------------------------------------------------------------------------

任职时长统计(离职)：按岗位职级统计所有

SELECT ltg.class_name as type, atg.class_name, count( 1 ) as group_count
FROM (
SELECT TIMESTAMPDIFF( MONTH, p.START_DATE, p.end_date ) AS age, d.EMPLOYEE_POSITION_LEVEL
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id
INNER JOIN ams_personal_relation r
ON r.PERSONAL_CODE = p.id
INNER JOIN ams_organization o
ON o.org_brnch_code = r.ORG_BRNCH_CODE
INNER JOIN ams_position o1
ON r.position_code = o1.position_code
WHERE p.BL_USE = ‘0’ AND p.PS_EMP_NO IS NOT NULL AND p.PERSONAL_NAME NOT LIKE ‘%测试%’ AND ( p.ID_CARD IS NOT NULL AND p.ID_CARD != ‘’ ) AND p.END_DATE >= ‘2022-08-01’ AND p.END_DATE <= ‘2022-08-31’ AND r.BGN_VALID_TIME <= p.END_DATE AND p.END_DATE <= r.END_VALID_TIME AND o.bgn_valid_time <= p.END_DATE AND p.END_DATE <= o.end_valid_time AND o1.start_date <= p.END_DATE AND p.END_DATE <= o1.END_DATE ) t
INNER JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’
INNER JOIN statistics ltg
ON ltg.class_value = t.EMPLOYEE_POSITION_LEVEL AND ltg.template_id = ‘T002’ GROUP BY atg.group_id,ltg.group_id;

任职时长统计(离职)：按岗位职级统计所有(中通总部）

SELECT ltg.class_name as type, atg.class_name, count( 1 ) as group_count
FROM (
SELECT TIMESTAMPDIFF( MONTH, p.START_DATE, p.end_date ) AS age, d.EMPLOYEE_POSITION_LEVEL
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id
INNER JOIN ams_personal_relation r
ON r.PERSONAL_CODE = p.id
INNER JOIN ams_organization o
ON o.org_brnch_code = r.ORG_BRNCH_CODE
INNER JOIN ams_position o1
ON r.position_code = o1.position_code
WHERE p.BL_USE = ‘0’ AND p.PS_EMP_NO IS NOT NULL AND p.PERSONAL_NAME NOT LIKE ‘%测试%’ AND ( p.ID_CARD IS NOT NULL AND p.ID_CARD != ‘’ ) AND p.END_DATE >= ‘2022-08-01’ AND p.END_DATE <= ‘2022-08-31’ AND r.BGN_VALID_TIME <= p.END_DATE AND p.END_DATE <= r.END_VALID_TIME AND o.bgn_valid_time <= p.END_DATE AND p.END_DATE <= o.end_valid_time AND o1.start_date <= p.END_DATE AND p.END_DATE <= o1.END_DATE and (o.org_brnch_uniq_path_name like ‘/总裁办%’ or o.org_brnch_uniq_path_name like ‘/网络管理中心%’ or o.org_brnch_uniq_path_name like ‘/市场营销中心%’ or o.org_brnch_uniq_path_name like ‘/转运管理中心%’ or o.org_brnch_uniq_path_name like ‘/服务质量中心%’ or o.org_brnch_uniq_path_name like ‘/财务管理中心%’ or o.org_brnch_uniq_path_name like ‘/人力资源中心%’ or o.org_brnch_uniq_path_name like ‘/IT信息中心%’ or o.org_brnch_uniq_path_name like ‘/安全监察中心%’ or o.org_brnch_uniq_path_name like ‘/公共事务管理中心%’ or o.org_brnch_uniq_path_name like ‘/营运办%’ or o.org_brnch_uniq_path_name like ‘/陆运管理中心%’ or o.org_brnch_uniq_path_name like ‘/综合物流中心%’ or o.org_brnch_uniq_path_name like ‘/资产管理中心%’ or o.org_brnch_uniq_path_name like ‘/资产管理中心/采购部%’ or o.org_brnch_uniq_path_name like ‘/行政部%’ or o.org_brnch_uniq_path_name like ‘/审计部%’ ) ) t
INNER JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’
INNER JOIN statistics ltg
ON ltg.class_value = t.EMPLOYEE_POSITION_LEVEL AND ltg.template_id = ‘T002’ GROUP BY atg.group_id,ltg.group_id;

任职时长统计(离职)：按岗位职级统计所有(省区汇总）

SELECT ltg.class_name as type, atg.class_name, count( 1 ) as group_count
FROM (
SELECT TIMESTAMPDIFF( MONTH, p.START_DATE, p.end_date ) AS age, d.EMPLOYEE_POSITION_LEVEL
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id
INNER JOIN ams_personal_relation r
ON r.PERSONAL_CODE = p.id
INNER JOIN ams_organization o
ON o.org_brnch_code = r.ORG_BRNCH_CODE
INNER JOIN ams_position o1
ON r.position_code = o1.position_code
WHERE p.BL_USE = ‘0’ AND p.PS_EMP_NO IS NOT NULL AND p.PERSONAL_NAME NOT LIKE ‘%测试%’ AND ( p.ID_CARD IS NOT NULL AND p.ID_CARD != ‘’ ) AND p.END_DATE >= ‘2022-08-01’ AND p.END_DATE <= ‘2022-08-31’ AND r.BGN_VALID_TIME <= p.END_DATE AND p.END_DATE <= r.END_VALID_TIME AND o.bgn_valid_time <= p.END_DATE AND p.END_DATE <= o.end_valid_time AND o1.start_date <= p.END_DATE AND p.END_DATE <= o1.END_DATE and (o.org_brnch_uniq_path_name like ‘/上海公司%’ or o.org_brnch_uniq_path_name like ‘/福建省公司%’ or o.org_brnch_uniq_path_name like ‘/北京公司%’ or o.org_brnch_uniq_path_name like ‘/河北省公司%’ or o.org_brnch_uniq_path_name like ‘/山西省公司%’ or o.org_brnch_uniq_path_name like ‘/江西省公司%’ or o.org_brnch_uniq_path_name like ‘/河南省公司%’ or o.org_brnch_uniq_path_name like ‘/湖北省公司%’ or o.org_brnch_uniq_path_name like ‘/湖南省公司%’ or o.org_brnch_uniq_path_name like ‘/四川省公司%’ or o.org_brnch_uniq_path_name like ‘/贵州省公司%’ or o.org_brnch_uniq_path_name like ‘/云南省公司%’ or o.org_brnch_uniq_path_name like ‘/重庆公司%’ or o.org_brnch_uniq_path_name like ‘/广西省公司%’ or o.org_brnch_uniq_path_name like ‘/黑龙江省公司%’ or o.org_brnch_uniq_path_name like ‘/吉林省公司%’ or o.org_brnch_uniq_path_name like ‘/辽宁省公司%’ or o.org_brnch_uniq_path_name like ‘/陕西省公司%’ or o.org_brnch_uniq_path_name like ‘/新疆公司%’ or o.org_brnch_uniq_path_name like ‘/甘肃省公司%’ or o.org_brnch_uniq_path_name like ‘/浙江省公司%’ or o.org_brnch_uniq_path_name like ‘/江苏省公司%’ or o.org_brnch_uniq_path_name like ‘/山东省公司%’ or o.org_brnch_uniq_path_name like ‘/安徽省公司%’ or o.org_brnch_uniq_path_name like ‘/广东省公司%’ ) ) t
INNER JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’
INNER JOIN statistics ltg
ON ltg.class_value = t.EMPLOYEE_POSITION_LEVEL AND ltg.template_id = ‘T002’ GROUP BY atg.group_id,ltg.group_id;

#-----------------------------------------------------------------------------------------------------------------

任职时长统计(离职)：按省区员工统计所有

SELECT ltg.class_name as type, atg.class_name, count( 1 ) as group_count
FROM (
SELECT TIMESTAMPDIFF( MONTH, p.START_DATE, p.end_date ) AS age, r.position_code
FROM ams_personal_info p
INNER JOIN ams_personal_info_detail d
ON d.id = p.id
INNER JOIN ams_personal_relation r
ON r.PERSONAL_CODE = p.id
INNER JOIN ams_organization o
ON o.org_brnch_code = r.ORG_BRNCH_CODE
INNER JOIN ams_position o1
ON r.position_code = o1.position_code
WHERE p.BL_USE = ‘0’ AND p.PS_EMP_NO IS NOT NULL AND p.PERSONAL_NAME NOT LIKE ‘%测试%’ AND ( p.ID_CARD IS NOT NULL AND p.ID_CARD != ‘’ ) AND p.END_DATE >= ‘2022-08-01’ AND p.END_DATE <= ‘2022-08-31’ AND r.BGN_VALID_TIME <= p.END_DATE AND p.END_DATE <= r.END_VALID_TIME AND o.bgn_valid_time <= p.END_DATE AND p.END_DATE <= o.end_valid_time AND o1.start_date <= p.END_DATE AND p.END_DATE <= o1.END_DATE ) t
INNER JOIN statistics atg
ON atg.class_value = t.age AND atg.template_id = ‘T01’
INNER JOIN statistics ltg
ON ltg.class_value = t.position_code AND ltg.template_id = ‘T003’ GROUP BY atg.group_id,ltg.group_id;