工作内容,不对外开放
一、Exists可能会让sql巨慢
hpfm_unit中unit_type_code = ‘D’ 表示它是部门数据,unit_type_code = 'C’表示它是公司,unit_type_code = ‘G’ 表示这条数据是集团。
一般的,集团下面是公司,公司下面是部门,部门下面还可能是部门。
我们要从hpfm_unit中查找部门编码或部门名称LIKE '%DEP1-A%'的数据,然后找到这些部门数据后,再全表遍历hpfm_position表,拿到(这些部门)及其(它们的父级)和(父级的父级)数据。也就是说白了,找到一个部门后还要找到该部门的父部门和它所属的公司它所属的集团
方式一:
SELECT
*
FROM
hpfm_unit u
JOIN hpfm_unit_tl utl ON utl.unit_id = u.unit_id
AND utl.lang = 'zh_CN'
LEFT JOIN hpfm_unit pu ON pu.unit_id = u.parent_unit_id
LEFT JOIN hpfm_company hc ON hc.company_id = u.company_id
WHERE
1 = 1
AND u.tenant_id = 0
AND EXISTS (
SELECT
1
FROM
hpfm_unit ut
WHERE
1 = 1
AND ( ut.unit_name LIKE '%DEP1-A%' OR ut.unit_code LIKE '%DEP1-A%' )
AND ut.unit_type_code = 'D'
AND ( ut.unit_id = u.unit_id OR ut.level_path LIKE concat( u.level_path, '|%' ))
)
性能:查询了60s,因为它的执行逻辑就是
①从hpfm_unit中查找查找部门编码或部门名称LIKE '%DEP1-A%'的数据,找到这些部门数据
②全表遍历hpfm_position表,拿到那些部门及其它们的父级和父级的父级数据。
方式二:
select
DISTINCT
t3.unit_id,
t3.unit_code,
t3.unit_name,
t3.unit_type_code,
t3.tenant_id,
t3.description,
t3.order_seq,
t3.parent_unit_id,
t3.supervisor_flag,
t3.enabled_flag,
t3.level_path,
t3.unit_company_id,
t3.object_version_number,
t3.company_id,
t3.quick_index,
t3.phoneticize,
t3.parent_unit_name,
t3.unit_company_name,
t3.company_name
from
(
SELECT
t1.level_path
FROM
(
SELECT
t.level_path,
t.unit_code,
tl.unit_name
FROM
hpfm_unit t
JOIN hpfm_unit_tl tl ON tl.unit_id = t.unit_id
AND tl.lang = 'zh_CN'
WHERE
t.unit_type_code = 'D'
AND t.tenant_id = 0
) t1
WHERE
t1.unit_name LIKE '%DEP1-A%'
OR t1.unit_code LIKE '%DEP1-A%'
) t2 ,
(
select
u.unit_id,
u.unit_code,
ut.unit_name,
u.unit_type_code,
u.tenant_id,
ut.description,
u.order_seq,
u.parent_unit_id,
u.supervisor_flag,
u.enabled_flag,
u.level_path,
u.unit_company_id,
u.object_version_number,
u.company_id,
u.quick_index,
u.phoneticize,
put.unit_name parent_unit_name,
uct.unit_name unit_company_name,
hc.company_name
from
hpfm_unit u
join hpfm_unit_tl ut on ut.unit_id = u.unit_id and ut.lang = 'zh_CN'
left join hpfm_unit pu on pu.unit_id = u.parent_unit_id
left join hpfm_unit_tl put on pu.unit_id = put.unit_id and put.lang = 'zh_CN'
left join hpfm_unit uc on u.unit_company_id = uc.unit_id
left join hpfm_unit_tl uct on uc.unit_id = uct.unit_id and uct.lang = 'zh_CN'
left join hpfm_company hc on hc.company_id = u.company_id
where
1 = 1
and u.tenant_id = 0
)t3
WHERE
( t2.level_path LIKE concat( t3.level_path, '|%' ) OR t2.level_path = t3.level_path )
执行仅用0.5s,因为它用到了该表的索引
二、练习
我们要从hpfm_position中查找岗位编码或岗位名称LIKE ‘%POSITION3-2%’ 的数据,然后找到这些岗位数据后,再全表遍历hpfm_position表,拿到(这些岗位)及其(它们的父级)和(父级的父级)数据。也就是说白了,找到一个岗位后还要找到该岗位是父岗位和它父岗位的父岗位。
SELECT
t3.position_id,
t3.tenant_id,
t3.unit_id,
t3.parent_position_id,
t3.parent_id,
t3.position_code,
t3.description,
t3.order_seq,
t3.supervisor_flag,
t3.enabled_flag,
t3.level_path,
t3.object_version_number
FROM
(
SELECT
t1.level_path
FROM
(
SELECT
hp.level_path,
hp.position_code,
hpt.position_name
FROM
hpfm_position hp
JOIN hpfm_position_tl hpt ON hpt.position_id = hp.position_id
AND hpt.lang = 'zh_CN'
WHERE
hp.tenant_id = 0
) t1
WHERE
t1.position_name LIKE '%POSITION3-2%'
OR t1.position_code LIKE '%POSITION3-2%'
) t2,
(
select
hhp.position_id,
hhp.tenant_id,
hhp.unit_id,
hhp.unit_company_id,
hhp.parent_position_id ,
hhp.parent_position_id parent_id,
hhp.position_code,
hhp.description,
hhp.order_seq,
hhp.supervisor_flag,
hhp.enabled_flag,
hhp.level_path,
hhp.object_version_number
from hpfm_position hhp
WHERE
hhp.tenant_id = 0
) t3
WHERE
( t2.level_path LIKE concat( t3.level_path, '|%' ) OR t3.level_path = t2.level_path )
select
DISTINCT
t3.unit_id,
t3.unit_code,
t3.unit_type_code,
t3.tenant_id,
t3.order_seq,
t3.parent_unit_id,
t3.supervisor_flag,
t3.enabled_flag,
t3.level_path,
t3.unit_company_id,
t3.object_version_number,
t3.company_id,
t3.quick_index,
t3.phoneticize
from
(
SELECT
t1.level_path
FROM
(
SELECT
t.level_path,
t.unit_code,
tl.unit_name
FROM
hpfm_unit t
JOIN hpfm_unit_tl tl ON tl.unit_id = t.unit_id
AND tl.lang = 'zh_CN'
WHERE
t.tenant_id = 0
) t1
WHERE
t1.unit_name LIKE '%DEP1-A%'
OR t1.unit_code LIKE '%DEP1-A%'
) t2 ,
(
select
u.unit_id,
u.unit_code,
u.unit_type_code,
u.tenant_id,
u.order_seq,
u.parent_unit_id,
u.supervisor_flag,
u.enabled_flag,
u.level_path,
u.unit_company_id,
u.object_version_number,
u.company_id,
u.quick_index,
u.phoneticize
from
hpfm_unit u
)t3
WHERE
( t2.level_path LIKE concat( t3.level_path, '|%' ) OR t2.level_path = t3.level_path )