Day10—SQL那些事（特殊场景的查询）

BlackTurn

已于 2024-05-07 16:56:05 修改

阅读量285

点赞数

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

于 2023-11-13 15:02:56 首次发布

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

版权

MYSQL进阶专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

1、只想查一个字段却不得不左连接好多张表
2、左连接的时候只想取最后一条数据
3、select的字段不是固定的
4、mysql逗号隔开拆成多行
5、IFNULL的使用
5、一个字段逗号分隔的方式存储了多个员工编码
6、如何根据查询结果进一步筛选数据？

1、只想查一个字段却不得不左连接好多张表

只想查一个字段却不得不左连接好多张表，而且因为左连接的表太多还导致查出来的数据重复

原先的sql

SELECT
	sph.po_num,
	chh.visa_exemption_flag 
FROM
	sodr_po_header sph
	LEFT JOIN sodr_po_line spl ON spl.po_header_id = sph.po_header_id
	LEFT JOIN cux_sprm_pr_line_hoc_assign csplha ON csplha.pr_line_id = spl.pr_line_id
	LEFT JOIN cux_hoc_header chh ON csplha.hoc_header_id = chh.hoc_header_id 
WHERE
	1 = 1 
	AND sph.po_header_id = '22993' 
	AND sph.tenant_id = 38

优化后的

SELECT
sph.po_num,
(select
			chh.visa_exemption_flag 
			from
				sodr_po_line spl 
			LEFT JOIN cux_sprm_pr_line_hoc_assign csplha ON csplha.pr_line_id = spl.pr_line_id 
			LEFT JOIN cux_hoc_header chh ON csplha.hoc_header_id = chh.hoc_header_id 
			where spl.po_header_id  = sph.po_header_id
			limit 1 ) as visa_exemption_flag 
FROM
	sodr_po_header sph
WHERE
	1 = 1 
	AND sph.po_header_id = '22993' 
	AND sph.tenant_id = 38

2、左连接的时候只想取最后一条数据

改动前sql：左连接时会连接所有数据

SELECT
	sprl.*
FROM
	sprm_pr_line sprl
	LEFT JOIN cux_sprm_pr_line_hoc_assign splha ON sprl.pr_line_id = splha.pr_line_id
	LEFT JOIN cux_hoc_line hol ON splha.hoc_line_id = hol.line_id
WHERE
	sprl.pr_line_id = 257198

改动后的sql：左连接时连接最后一条更新的数据

SELECT
	sprl.*
FROM
	sprm_pr_line sprl
	LEFT JOIN (select hoc_line_id,pr_line_id,MAX(last_update_date) as last_update_date from cux_sprm_pr_line_hoc_assign GROUP BY pr_line_id) temp on temp.pr_line_id = sprl.pr_line_id
	LEFT JOIN cux_sprm_pr_line_hoc_assign splha ON (temp.pr_line_id = splha.pr_line_id AND splha.last_update_date=temp.last_update_date)
	LEFT JOIN cux_hoc_line hol ON splha.hoc_line_id = hol.line_id
WHERE
	sprl.pr_line_id = 257198

3、select的字段不是固定的

PromptServiceImpl

    public List<Map<String, Object>> exportPrompt(Prompt prompt, PageRequest pageRequest, ExportParam exportParam) {
        prompt.setPromptKey(StringUtils.hasText(prompt.getPromptKey()) ? prompt.getPromptKey().toUpperCase(Locale.ROOT) : prompt.getPromptKey());
        prompt.setPromptCode(StringUtils.hasText(prompt.getPromptCode()) ? prompt.getPromptCode().toUpperCase(Locale.ROOT) : prompt.getPromptCode());
        prompt.setDescription(StringUtils.hasText(prompt.getDescription()) ? prompt.getDescription().toUpperCase(Locale.ROOT) : prompt.getDescription());
        // 1.10版本的多语言导出是一种动态列的导出，导出的语言列取决于fd_language表中有多少种语言
        List<org.hzero.mybatis.domian.Language> languages = LanguageHelper.languages();
        List<Map<String, Object>> dataList = promptRepository.exportPrompt(prompt, languages);
        return dataList;

    }

PromptRepositoryImpl

    public List<Map<String, Object>> exportPrompt(Prompt prompt, List<Language> languages) {
        /**
     * 查询出的数据应是下面这种结构
         {
             "tenant_id": 0,
             "promptKey": "hpfm.dataSourceDriver",
             "promptCode": "model.DataDimconfig",
             "description:en_US": "Data Dimension Configuration",
             "description:zh_CN": "数据维度配置",
             "description:ja_JP": "データ次元の構成"
         }
     */
        return promptMapper.exportPrompt(prompt.getTenantId(), prompt.getPromptKey(), prompt.getPromptCode(), prompt.getDescription(), prompt.getKeyword(), prompt.getDataOrigin(), languages);
    }

PromptMapper

    <select id="exportPrompt" parameterType="org.hzero.platform.domain.entity.Prompt" resultType="java.util.Map">
        <bind name="currentLang" value="@io.choerodon.mybatis.helper.LanguageHelper@language()" />
        SELECT
            hp.prompt_key promptKey,
            hp.prompt_code promptCode,
            hp.tenant_id,
            <foreach collection="languages" separator="," item="language">
            ( SELECT description FROM hpfm_prompt hp_tmp WHERE lang = #{language.code} and hp_tmp.prompt_key = hp.prompt_key and hp_tmp.prompt_code = hp.prompt_code and hp_tmp.tenant_id = hp.tenant_id) as 'description:${language.code}'
            </foreach>
        from
            hpfm_prompt hp
        where
        lang = #{currentLang}
        AND (hp.tenant_id = 0 <if test="tenantId != null and tenantId != 0">OR hp.tenant_id = #{tenantId}</if>)
        <if test="tenantId != null and tenantId != 0">
            AND hp.prompt_id NOT IN (
            SELECT hpr.prompt_id
            FROM hpfm_prompt hpr
            JOIN hpfm_prompt hpr1 ON (hpr.prompt_key = hpr1.prompt_key
                AND hpr.prompt_code = hpr1.prompt_code
                AND hpr.lang = hpr1.lang
                AND hpr1.tenant_id = #{tenantId})
            WHERE hpr.tenant_id = 0
            )
        </if>
        <if test="promptKey != null">
            <bind name="keyLike" value="'%'+ promptKey + '%'"/>
            AND UPPER(hp.prompt_key) LIKE #{keyLike}
        </if>
        <if test="promptCode != null">
            <bind name="codeLike" value="'%'+ promptCode + '%'"/>
            AND UPPER(hp.prompt_code) LIKE #{codeLike}
        </if>
        <if test="description != null">
            <bind name="descriptionLike" value="'%' + description + '%'"/>
            AND UPPER(hp.description) LIKE #{descriptionLike}
        </if>
        <if test="keyword != null and keyword != ''">
            <bind name="keywordLike" value="'%' + keyword + '%'"/>
            AND (hp.prompt_key LIKE #{keywordLike} OR hp.prompt_code LIKE #{keywordLike} OR hp.description LIKE #{keywordLike})
        </if>
        <if test="tenantId != null">
            <if test="dataOrigin == 'CUS'">
                AND hp.tenant_id = #{tenantId}
            </if>
            <if test="dataOrigin == 'PRE'">
                AND hp.tenant_id != #{tenantId}
            </if>
        </if>
    </select>

4、mysql逗号隔开拆成多行

在这里插入图片描述
建表语句

CREATE TABLE tbl_name(     id       int(11)    not null auto_increment,     userName varchar(100)    not null, PRIMARY KEY(id) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;  insert into tbl_name values (1,'a,aa,aaa'); insert into tbl_name values (2,'b,bb'); insert into tbl_name values (3,'c,cc')

需求：
userName是逗号分隔存储的，而且可能存了1个2个3个等等个数据，如何把它按照逗号分隔开来展示成多列

查询sql

SELECT
	a.*,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.userName, ',', n.digit ), ',', - 1 ) AS VALUE
FROM
	tbl_name a
	JOIN ( SELECT 1 AS digit UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) n ON CHAR_LENGTH( a.userName ) - CHAR_LENGTH(REPLACE ( a.userName, ',', '' )) >= n.digit - 1 
WHERE
	a.id =1

5、IFNULL的使用

入库金额=合同行单价*接收数量，接收数量来自hcbm_fulfill_receive_transaction表的quantity，合同行单价来自合同行表，这两张表通过合同行id去关联
当合同发生过变更时，hcbm_fulfill_receive_transaction表里面存的合同行id 得关联合同行表的initial_id 而不是关联合同行表的id

    <select id="selectWareHouseInfos" resultType="io.hcbm.erpinvoicereport.dto.WarehouseDTO">
        SELECT
            relateContractNumber,
            sum( hfrt3.quantity ) AS quantity,
            sum(
                IFNULL(hfrt3.quantity * IFNULL(
                    <!--入库金额=合同行单价*接收数量，接收数量来自hcbm_fulfill_receive_transaction表的quantity，合同行单价来自合同行表，这两张表通过合同行id去关联-->
                    <!--当合同发生过变更时，hcbm_fulfill_receive_transaction表里面存的合同行id 得关联 合同行表的initial_id 而不是关联 合同行表的id-->
                    (
                    SELECT
                        hcontractLine.price
                    FROM
                        hcbm_contract hcontract
                        LEFT JOIN hcbm_contract_line hcontractLine ON hcontract.id = hcontractLine.contract_id
                    WHERE
                        hcontract.contract_number = hfrt3.relateContractNumber
                        AND hcontractLine.materials_code = hfrt3.materials_code
                        AND hcontractLine.id = hfrt3.contract_line_id
                        limit 1
                    ),(
                    SELECT
                        hcontractLine.price
                    FROM
                        hcbm_contract hcontract
                        LEFT JOIN hcbm_contract_line hcontractLine ON hcontract.id = hcontractLine.contract_id
                    WHERE
                        hcontract.contract_number = hfrt3.relateContractNumber
                        AND hcontractLine.materials_code = hfrt3.materials_code
                        AND hcontractLine.initial_id = hfrt3.contract_line_id
                        limit 1
                    )),0 )) AS warehousingAmount,
            max( hfrt3.transaction_date ) AS transaction_date
        FROM
            (
            SELECT
                sum( hfrt.quantity ) AS quantity,
                hfrt.materials_code,
                hfrt.contract_line_id,
                max( hfrt.transaction_date ) AS transaction_date,
                hfrt.po_num AS relateContractNumber
            FROM
                hcbm_fulfill_receive_transaction hfrt
            WHERE
                1 = 1
                AND hfrt.transaction_type IN ( 'DELIVER', 'RETURN TO RECEIVING' )
                AND hfrt.materials_code IS NOT NULL
                AND hfrt.contract_line_id IS NOT NULL
                AND hfrt.quantity IS NOT NULL
            GROUP BY
                hfrt.po_num,
                hfrt.materials_code,
                hfrt.contract_line_id UNION
            SELECT
                sum( hfrt.quantity ) AS quantity,
                REPLACE ( ltrim( REPLACE ( hfrt.materials_code, '0', ' ' )), ' ', '0' ) AS materials_code,
                hfrt.contract_line_id,
                max( hfrt.transaction_date ) AS transaction_date,
                hfrt.zzhtno AS relateContractNumber
            FROM
                hcbm_fulfill_receive_transaction hfrt
            WHERE
                1 = 1
                AND hfrt.transaction_type IN ( '101', '102', '122', '123', '161', '162' )
                AND hfrt.materials_code IS NOT NULL
                AND hfrt.contract_line_id IS NOT NULL
                AND hfrt.quantity IS NOT NULL
            GROUP BY
                hfrt.zzhtno,
                hfrt.materials_code,
                hfrt.contract_line_id
            ) hfrt3
        WHERE
        hfrt3.relateContractNumber in
        <foreach collection="relateContractNumbers" open="(" close=")" separator="," item="contractNumber">
            #{contractNumber}
        </foreach>
        GROUP BY
        relateContractNumber
    </select>

完整sql如下

-- --------------------------------------
-- #0003 2024-03-11 09:31:07.994 DEBUG 8 --- [  XNIO-3 task-2] i.h.e.i.m.E.selectWareHouseInfos         : ==>  
select relateContractNumber, sum(hfrt3.quantity) as quantity
	, sum(IFNULL(hfrt3.quantity * IFNULL((
		select hcontractLine.price
		from hcbm_contract hcontract
			left join hcbm_contract_line hcontractLine on hcontract.id = hcontractLine.contract_id
		where hcontract.contract_number = hfrt3.relateContractNumber
			and hcontractLine.materials_code = hfrt3.materials_code
			and hcontractLine.id = hfrt3.contract_line_id
		limit 1
	), (
		select hcontractLine.price
		from hcbm_contract hcontract
			left join hcbm_contract_line hcontractLine on hcontract.id = hcontractLine.contract_id
		where hcontract.contract_number = hfrt3.relateContractNumber
			and hcontractLine.materials_code = hfrt3.materials_code
			and hcontractLine.initial_id = hfrt3.contract_line_id
		limit 1
	)), 0)) as warehousingAmount
	, max(hfrt3.transaction_date) as transaction_date
from (
	select sum(hfrt.quantity) as quantity, hfrt.materials_code, hfrt.contract_line_id
		, max(hfrt.transaction_date) as transaction_date, hfrt.po_num as relateContractNumber
	from hcbm_fulfill_receive_transaction hfrt
	where 1 = 1
		and hfrt.transaction_type in ('DELIVER', 'RETURN TO RECEIVING')
		and hfrt.materials_code is not null
		and hfrt.contract_line_id is not null
		and hfrt.quantity is not null
	group by hfrt.po_num, hfrt.materials_code, hfrt.contract_line_id
	union
	select sum(hfrt.quantity) as quantity
		, REPLACE(ltrim(REPLACE(hfrt.materials_code, '0', ' ')), ' ', '0') as materials_code
		, hfrt.contract_line_id, max(hfrt.transaction_date) as transaction_date, hfrt.zzhtno as relateContractNumber
	from hcbm_fulfill_receive_transaction hfrt
	where 1 = 1
		and hfrt.transaction_type in (
			'101', 
			'102', 
			'122', 
			'123', 
			'161', 
			'162'
		)
		and hfrt.materials_code is not null
		and hfrt.contract_line_id is not null
		and hfrt.quantity is not null
	group by hfrt.zzhtno, hfrt.materials_code, hfrt.contract_line_id
) hfrt3
where hfrt3.relateContractNumber in ('1003221200088', '1003230200093', '1003230300100', '1003230300101', '1003231000015', '1003231100058', '1003231200001', '1023230300004', 'KJ2020156-000008S', 'KJ2022303-000005S')
group by relateContractNumber;

5、一个字段逗号分隔的方式存储了多个员工编码

cinv_quality_to_portal_inf表的quality_person字段是逗号分隔存储员工编码

在这里插入图片描述

下面是hpfm_employee表

可以通过下面的方式查询到员工名称

SELECT
	a.receipt_number,
	a.quality_person,
	GROUP_CONCAT( DISTINCT e.`NAME` SEPARATOR ', ' ) AS quality_inspector 
FROM
	cinv_quality_to_portal_inf a
	LEFT JOIN hzero_platform.hpfm_employee e ON FIND_IN_SET( e.employee_num, a.quality_person ) > 0

也可以通过下面的方式模糊搜索

        SELECT
        a.receipt_number,
        a.quality_person,
        GROUP_CONCAT( DISTINCT e.`NAME` SEPARATOR ', ' ) AS quality_inspector
        FROM
        cinv_quality_to_portal_inf a
        LEFT JOIN hzero_platform.hpfm_employee e ON FIND_IN_SET( e.employee_num, a.quality_person ) > 0
        WHERE
        ( a.receipt_number, a.quality_person ) IN
        <foreach collection="qs" index="index" item="item" open="(" separator="," close=")">
            (#{item.receiptNumber}, #{item.qualityPerson})
        </foreach>
        GROUP BY
        a.receipt_number,
        a.quality_person

6、如何根据查询结果进一步筛选数据？

以前是现有条件后查数据，现在是查出数据，根据数据的类型来进一步筛选合适的数据
现在要实现“根据已有的条件查询到100条合同数据，然后对这100条数据做过滤，当合同性质是框架的，只允许查询到创建人是自己的；如果合同性质不是框架协议，则不做这层过滤”的效果

select * from hcbm_contract hc where 1=1 
and (hc.property_code != 'AGREEMENT') or (hc.property_code = 'AGREEMENT' and hc.created_by = #{__userId})

BlackTurn

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录