SQL优化记录

已于 2022-09-17 17:29:10 修改
阅读量251 收藏
点赞数 1
分类专栏： Mysql优化 文章标签： javascript 开发语言 ecmascript
于 2022-09-06 14:49:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_25881443/article/details/126725155
版权

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

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 <![CDATA[>=]]> #{ofWorkingHoursServiceDTO.timeStart}
and p.END_DATE <![CDATA[<=]]> #{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;

生命几十年3万天
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
记录一次SQL优化
12-14
一大早到公司，加班一宿的同事跟我抱怨，说，一个表只有8w条数据，需要更新一列，根据目前进度估算，大概需要3个小时，即使... 　5、执行sql语句，还是很慢，explain显示可能能用到我们刚才添加的索引，但是rows上来看
mysql常用sql语句执行时间记录(sql优化)
baidu_16416841的博客
09-21 319
###1、查询单表 数据量：3200000条 1.1、数量查询 select count(1) from users --耗时：12.997s select count(*) from users --耗时：12.449s select count(id) from users --id是主键 --耗时：12.414s select count(`password`) from users --password，字符型，普通字段无索引 --耗时：4.869s select count(`stat
记录一次sql语句的优化
weixin_55633991的博客
12-09 158
这个是一开始的sql语句 <select id="queryLoRaWANWaterMeter" parameterType="java.util.HashMap" resultType="java.util.HashMap"> select distinct DATE_FORMAT(c.DATA_TIME,'%Y-%m-%d %H:%i:%s') as dataTime, c1.CUST_NAME as custName, c1.CUST_CODE as custId.
8个SQL语句常用优化技巧
热门推荐
CSDN高校俱乐部官方博客
10-23 3万+
要提高SQL语句的执行效率，最常见的方法就是建立索引，以及尽量避免全表扫描。在本章MySQL教程中，UncleToo给大家整理一些常见的SQL优化技巧，避免全表扫描。一个简单的优化，也许能让你的SQL执行效率提高几倍，甚至几十倍。 1、避免在where子句中使用 is null 或 is not null 对字段进行判断。 如： select id from table
SQL优化工作记录
爪哇岛咖啡种植农场
11-18 281
记录一次在工作中的数据库使用方面的优化
SQL优化
简书的专栏
10-25 2134
文章目录一、Linux下RPM版MYSQL安装、启停1.1 环境1.2 MySQL版本1.3 MySQL安装二、MySQL启动问题、配置文件、编码问题三、MySQL分层、存储引擎3.1 MySQL逻辑分层3.2 存储引擎四、SQL解析过程、索引、B树4.1 SQL需要优化的原因4.2 SQL的编写与解析过程4.3 索引的利弊五、B树与索引5.1 B树5.2 索引六、SQL优化准备6.1 SQL性能...
SQL优化 SQL优化软件 SQL优化工具
12-14
SQL优化是数据库管理中的关键环节，它涉及到提升查询性能、减少资源消耗以及改善系统整体效率。SQL优化软件和工具能够帮助数据库管理员（DBA）和开发人员找出性能瓶颈，优化查询逻辑，从而提高数据库系统的响应速度...
oracle-sql优化
08-03
6. **SQL优化过程**：定位问题SQL，检查执行计划，分析优化器统计信息，考虑表的记录数和索引状况，通过改写SQL、使用HINT、调整索引或进行表分析来优化。某些情况下，可能需要改变处理方式。 7. **好的SQL语句**：...
Sql优化.ppt
10-27
SQL 查询优化是数据库优化的重要部分，查询优化器是 SQL SERVER 中的一个组件，可以自动优化查询语句，提高查询效率。本文将详细介绍查询优化器的工作原理、SARG 的定义和应用、查询优化的 tips 等。 一、查询优化...
sql优化 策略
09-07
SQL 优化策略 SQL 优化是数据库性能优化的关键一步骤。通过优化 SQL 语句，数据库管理员可以提高数据库的查询速度，降低数据库的负载，提高数据库的整体性能。本文将总结一些常见的 SQL 优化策略，帮助数据库管理员...
sql优化记录
chenxing1990的专栏
01-29 968
sql优化
一则sql优化记录
xiaobose的博客
06-12 519
数据库巡检的时候，发现一条高耗sql。执行计划如下：
常用30种SQL查询语句优化方法
qq_26128879的博客
03-18 645
1、应尽量【避免在 where 子句中使用】【!=或<>操作符】，【否则引擎将放弃使用索引】而【进行全表扫描】。 2、对查询进行优化，应【尽量避免全表扫描】，首先应【考虑在 where 及 order by 涉及的列上】【建立索引】。 3、【应尽量避免在 where 子句中】对【字段进行 null 值判断】，【否则将导】致引擎放弃使用索引而进行【全表扫描】，如： select id from t where num is null 【可以在num上设置默认值0，确保表中num..
TinyVue v3.17.0 正式发布，推出了一款基于 Quill 2.0 的富文本编辑器，功能强大、开箱即用！
OpenTiny的博客
07-17 1232
本文由体验技术团队Kagol老师原创~我们非常高兴地宣布，2024年6月26日，TinyVue 发布了 v3.17.0 🎉。TinyVue 每次大版本发布，都会给大家带来一些实用的新特性，上一个版本我们重构了 chart-core，新增 CircleProcessChart 圆环进度图等6个新的图表组件，并增加了 Statistic 数据统计组件。表格图片超链接复制粘贴插入表情文件上传@提醒斜杆菜单v-auto-tip。
react 快速入门思维导图
最新发布
weixin_44565776的博客
07-18 330
只要掌握了这些react技能，基本就可以熟练使用react了

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值