工作内容,不对外开放
1、写sql的三步曲(案例)
SELECT
hu.unit_code,
hu.unit_name,
hp.position_code,
hp.position_name,
hea.employee_id,
he.employee_num,
he.name
FROM
hpfm_unit hu
JOIN
(
SELECT
huc.level_path
FROM
hpfm_employee_assign hea
JOIN hpfm_unit huc ON hea.unit_company_id = huc.unit_id
JOIN hpfm_employee_user heu ON hea.employee_id = heu.employee_id
WHERE
heu.user_id = #{userId}
GROUP BY
hea.unit_company_id
) hec
ON hu.level_path LIKE CONCAT(hec.level_path, '|%')
LEFT JOIN hpfm_position hp ON hp.unit_id = hu.unit_id
LEFT JOIN hpfm_employee_assign hea ON hea.position_id = hp.position_id
JOIN hpfm_employee he ON hea.employee_id = he.employee_id
一》列大纲
实现的功能:
我要根据当前登录用户查到他的所属公司的组织架构,还要找到公司下的所有用户
具体的实现:
①根据user_id查询hpfm_employee_user表拿到该用户绑定的员工(一个用户可能在多个公司下工作,所以是一对多),拿到employee_id
②根据employee_id查询hpfm_employee_assign表拿到员工绑定的公司unit_company_id(其实就是hpfm_unit表的unit_id)
③根据unit_company_id查询hpfm_unit表拿到公司的level_path
④根据公司的level_path查询hpfm_unit表拿到公司的子级公司子级部门等等,我们统称为list
⑤根据list里面的unit_id查询hpfm_position表拿到这些子公司子部门下的所有岗位list
⑥根据list查询hpfm_employee_assign表拿到这些岗位下的员工employee_id
⑦根据employee_id查询hpfm_employee表,拿到员工数据
二》多表join来减少查询次数
①②③就是user_id -> employee_id -> unit_company_id ->level_path
我们只需要hpfm_employee_user和hpfm_employee_assign和hpfm_unit表进行内连接就可以实现①②③的一步到位,也就是说给我一个用户id我就能找到该用户所属公司的level_path
⑤⑥⑦就是unit_id -> position_id -> employee_id
我们只需要hpfm_unit和hpfm_position和hpfm_employee_assign和hpfm_employee进行连接就可以实现unit_id到employee_id的一步到位,也就是給我一个unit_id我就能找到组织下的所有员工
三》如果该表仅仅是用作条件判断就用exists,如果是查询该表的字段就用join
你要关联一张表,又不查询这张表的任何字段,那就用Exists,不要用Join
2、写sql的三步曲(案例)
报表分组那里,可以对仪表板、标签、报表进行分组,hpfm_group表记录了分组信息,hpfm_group_assign表记录了分组分配信息(哪个报表分配给了哪个分组、哪个仪表板分配给了哪个分组)
我们现在根据用户的权限找到用户有权限看到的报表、仪表板、标签的id集合relationIds,我现在想根据relationIds拿到分组树
一》列大纲
- ①根据relation_id查找hrpt_group_assign表拿到group_id
- ②然后根据group_id查询hrpt_group表拿到level_path,
- ③然后根据level_path查询hrpt_group表拿到当前分组及其父级分组的集合
二》多表join来减少查询次数
①②就是releation_id -> group_id -> level_path
我们只需要将hrpt_group和hrpt_group_assign进行内连接后就可以实现①②的一步到位,也就是说给我一个报表id我就可以找到报表的所属分组的level_path
三》如果该表仅仅是用作条件判断就用exists,如果是查询该表的字段就用join
第③步很明显使用exists最合适
SELECT
hg.group_id,
hg.group_code,
hgt.group_name,
hg.group_type_code,
hg.enabled_flag,
hg.object_version_number,
hg.tenant_id,
htt.tenant_name,
hg.level_path,
hg.order_seq,
hg.parent_id
FROM
hrpt_group hg
JOIN hrpt_group_tl hgt ON hg.group_id = hgt.group_id AND hgt.lang = #{lang}
JOIN hzero_platform.hpfm_tenant_tl htt ON hg.tenant_id = htt.tenant_id AND htt.lang = #{lang}
WHERE
EXISTS (
SELECT
hgg.level_path
FROM
hrpt_group hgg
JOIN hrpt_group_assign hga ON hga.group_id = hgg.group_id
WHERE
hgg.enabled_flag = 1
AND ( hga.relation_id IN
<foreach collection="relationIds" item="item" open="(" separator="," close=")">
#{item}
</foreach>
)
AND (hgg.group_id = hg.group_id OR hgg.level_path LIKE concat( hg.level_path, '|%'))
)
ORDER BY
hg.order_seq,
hg.group_id
3、查询时如果关联了a表,只是我们找符合a表中某个条件的数据,但是并没有查询a表的字段,那就用exists而不是用left join
错误的写法:
<select id="selectMessageList" parameterType="org.hzero.message.api.dto.UserMsgParamDTO" resultType="org.hzero.message.api.dto.UserMessageDTO">
<bind name="lang" value="@io.choerodon.mybatis.helper.LanguageHelper@language()" />
select
hum.user_message_id,
hum.message_id,
hum.user_message_type_code,
hum.read_flag,
hum.object_version_number,
hum.creation_date,
<if test="userMessageTypeCode == 'MSG'">
hm.message_type_code,
hm.subject subject,
</if>
<if test="userMessageTypeCode != 'MSG'">
hn.title subject,
</if>
<if test="withContent and userMessageTypeCode == 'MSG'">
hm.content content,
</if>
<if test="withContent and userMessageTypeCode != 'MSG'">
hnp.notice_body content,
</if>
hum.tenant_id,
ht.tenant_name
from hmsg_user_message hum
join hpfm_tenant_tl ht on ht.tenant_id = hum.tenant_id and ht.lang = #{lang}
<if test="userMessageTypeCode == 'MSG'">
join hmsg_message hm on hm.message_id = hum.message_id and hum.user_message_type_code = 'MSG' and hm.message_type_code = #{messageTypeCode}
left join hmsg_message_template hmt ON hm.template_code = hmt.template_code AND hm.lang = hmt.lang AND (hm.tenant_id = hmt.tenant_id or hmt.tenant_id = 0)
</if>
<if test="userMessageTypeCode != 'MSG'">
join hmsg_notice_published hnp on hum.message_id = hnp.published_id and hnp.published_status_code ='PUBLISHED' and hum.user_message_type_code != 'MSG'
join hmsg_notice hn on hnp.notice_id = hn.notice_id
</if>
where
hum.user_id = #{userId}
and hum.user_message_type_code = #{userMessageTypeCode}
<if test="userMessageTypeCode == null or userMessageTypeCode == ''">
and hum.user_message_type_code = 'MSG'
</if>
<if test="readFlag != null">
and hum.read_flag = #{readFlag}
</if>
<if test="fromDate != null">
and hum.creation_date >= #{fromDate}
</if>
<if test="toDate != null">
and hum.creation_date <= #{toDate}
</if>
<if test="subject != null and subject != '' and userMessageTypeCode == 'MSG'">
<bind name="subjectLike" value="'%'+subject+'%'"/>
and hm.subject LIKE #{subjectLike}
</if>
<if test="templateCodeList != null and templateCodeList.size() > 0 and userMessageTypeCode == 'MSG'">
and
<foreach collection="templateCodeList" index="index" item="item" open="(" separator=" or " close=")">
hm.template_code LIKE CONCAT(CONCAT('%', #{item}), '%')
</foreach>
</if>
<if test="subject != null and subject != '' and userMessageTypeCode != 'MSG'">
<bind name="subjectLike" value="'%'+subject+'%'"/>
and hn.title LIKE #{subjectLike}
</if>
<if test="userMessageTypeCode == 'MSG'">
<if test="messageCategoryCode != null and messageCategoryCode != ''">
and hmt.message_category_code = #{messageCategoryCode}
</if>
<if test="messageSubcategoryCode != null and messageSubcategoryCode != ''">
and hmt.message_subcategory_code = #{messageSubcategoryCode}
</if>
</if>
</select>
正确的写法:
<select id="selectMessageList" parameterType="org.hzero.message.api.dto.UserMsgParamDTO" resultType="org.hzero.message.api.dto.UserMessageDTO">
<bind name="lang" value="@io.choerodon.mybatis.helper.LanguageHelper@language()" />
select
hum.user_message_id,
hum.message_id,
hum.user_message_type_code,
hum.read_flag,
hum.object_version_number,
hum.creation_date,
<if test="userMessageTypeCode == 'MSG'">
hm.message_type_code,
hm.subject subject,
</if>
<if test="userMessageTypeCode != 'MSG'">
hn.title subject,
</if>
<if test="withContent and userMessageTypeCode == 'MSG'">
hm.content content,
</if>
<if test="withContent and userMessageTypeCode != 'MSG'">
hnp.notice_body content,
</if>
hum.tenant_id,
ht.tenant_name
from hmsg_user_message hum
join hpfm_tenant_tl ht on ht.tenant_id = hum.tenant_id and ht.lang = #{lang}
<if test="userMessageTypeCode == 'MSG'">
join hmsg_message hm on hm.message_id = hum.message_id and hum.user_message_type_code = 'MSG' and hm.message_type_code = #{messageTypeCode}
</if>
<if test="userMessageTypeCode != 'MSG'">
join hmsg_notice_published hnp on hum.message_id = hnp.published_id and hnp.published_status_code ='PUBLISHED' and hum.user_message_type_code != 'MSG'
join hmsg_notice hn on hnp.notice_id = hn.notice_id
</if>
where
hum.user_id = #{userId}
and hum.user_message_type_code = #{userMessageTypeCode}
<if test="userMessageTypeCode == null or userMessageTypeCode == ''">
and hum.user_message_type_code = 'MSG'
</if>
<if test="readFlag != null">
and hum.read_flag = #{readFlag}
</if>
<if test="fromDate != null">
and hum.creation_date >= #{fromDate}
</if>
<if test="toDate != null">
and hum.creation_date <= #{toDate}
</if>
<if test="subject != null and subject != '' and userMessageTypeCode == 'MSG'">
<bind name="subjectLike" value="'%'+subject+'%'"/>
and hm.subject LIKE #{subjectLike}
</if>
<if test="templateCodeList != null and templateCodeList.size() > 0 and userMessageTypeCode == 'MSG'">
and
<foreach collection="templateCodeList" index="index" item="item" open="(" separator=" or " close=")">
hm.template_code LIKE CONCAT(CONCAT('%', #{item}), '%')
</foreach>
</if>
<if test="subject != null and subject != '' and userMessageTypeCode != 'MSG'">
<bind name="subjectLike" value="'%'+subject+'%'"/>
and hn.title LIKE #{subjectLike}
</if>
<if test="userMessageTypeCode == 'MSG'">
<if test="messageCategoryCode != null and messageCategoryCode != ''">
and Exists(
select
1
from
hmsg_message_template hmt
where
hm.template_code = hmt.template_code
AND hm.lang = hmt.lang
AND (hm.tenant_id = hmt.tenant_id or hmt.tenant_id = 0)
AND hmt.message_category_code = #{messageCategoryCode}
)
</if>
<if test="messageSubcategoryCode != null and messageSubcategoryCode != ''">
and Exists(
select
1
from
hmsg_message_template hmt
where
hm.template_code = hmt.template_code
AND hm.lang = hmt.lang
AND (hm.tenant_id = hmt.tenant_id or hmt.tenant_id = 0)
AND hmt.message_subcategory_code = #{messageSubcategoryCode}
)
</if>
</if>
</select>