Day07—SQL那些事（快速写sql）

BlackTurn

已于 2023-11-13 14:53:57 修改

阅读量147

点赞数

分类专栏： MYSQL进阶文章标签：数据库 sql java

于 2023-02-27 20:40:56 首次发布

本文链接：https://blog.csdn.net/weixin_45014721/article/details/129248673

版权

MYSQL进阶专栏收录该内容

9 篇文章 0 订阅

订阅专栏

工作内容，不对外开放

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 &gt;= #{fromDate}
        </if>
        <if test="toDate != null">
            and hum.creation_date &lt;= #{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 &gt;= #{fromDate}
        </if>
        <if test="toDate != null">
            and hum.creation_date &lt;= #{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>