【业务功能篇23】mysql GROUP_CONCAT/ 行转列/函数记录

浅浅的花香味﹌ 2024-03-16 17:04 62阅读 0赞

> 在前面我们写过的一篇介绍复杂业务功能开发。
> 
> [【业务功能篇16】Springboot+mybatisplus+ShedLock框架根据一定的逻辑数据处理规则，定时任务生成告警单\_studyday1的博客-CSDN博客][16_Springboot_mybatisplus_ShedLock_studyday1_-CSDN]

然后我们在不良数预警逻辑中，sql的编写过程中，涉及到需要拼接两个字段信息，就是告警单的分析人和评审人，我们需要把这些用户信息，拼接成一个新的字段，叫 团队成员，表示这个单据相关的团队，后续可以用来判断给团队成员执行一些操作

那么在合并的时候，也可能出现有重复的分析人和评审人存在相同用户，导致重复，那么这里我们需求去重，那么就需要再将这个团队成员字符串信息 "用户A,用户B,用户C,用户A ",进行行转列，把每个用户都打印出一行，再用distinct去重，剩下 A,B,C三行数据，然后再用group\_concat,全部为一组合并起来，返回"用户A,用户B,用户C"

CREATE DEFINER=`root`@`%` FUNCTION `dpDeduplicate`(`str` text) RETURNS text CHARSET utf8mb4
    BEGIN
    	#Routine body goes here...
    	DECLARE result text CHARSET utf8mb4;  
    	SET result = ''; 
    	select GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', b.help_topic_id + 1), ',', - 1)) INTO result from (SELECT str) a
    	join mysql.help_topic b on b.help_topic_id < (length(str) - length(replace(str, ',', '')) + 1);
    	RETURN result;
    END

在对应的sql调用中如下：

-- 临时表： 编码分配责任人表 
            with table1 as (
                select item_code itemCode,product,project,category,sub_category subCategory,
                '管理员' as member,
                '管理员' as analysisBy,
                '管理员' as reviewBy
                from dwr_quality_ppm_code_f A
                <where>
                    is_set = '是'
                    <if test="queryParam.category != null and queryParam.category.size > 0">
                        and category in
                        <foreach collection="queryParam.category" separator="," item="item" open="(" close=")">
                            #{item}
                        </foreach>
                    </if>
                </where>
                group by item_code order by item_code
            ),
    				-- 临时表： 近一个月内预警过的问题单号，拆分出 一行就是一个单号 
    				table4 as (
                select SUBSTRING_INDEX(SUBSTRING_INDEX(a.mes_no, ',', b.help_topic_id + 1), ',', - 1) as mesNo from dwr_mt_production_problem_warn_f a
                join mysql.help_topic b on b.help_topic_id  <![CDATA[ < ]]> (length(a.mes_no) - length(replace(a.mes_no, ',', '')) + 1)
                where DATE_FORMAT( warn_date, '%Y-%m-%d') >= DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL 1 MONTH ), '%Y-%m-%d')
            ),
    				-- 临时表： 不包括t4临时表中已预警的问题单单号的 问题单明细表 
    				table2 as (
                select issure_no mesNo,item_code itemCode,fail_item failItem,vendor supplier,bad_qty badQty,instance_create_time,defect_name
                from txny.dwr_quality_qm_issure_feed_f A
                where instance_status != '4' and defect_name != '故障返修' and date_format(instance_create_time,'%Y-%m-%d') >= #{queryParam.startPeriod}
                and date_format(instance_create_time,'%Y-%m-%d') <![CDATA[ <= ]]> #{queryParam.endPeriod} and fail_item is not null
                /*where instance_status != '4' and date_format(instance_create_time,'%Y-%m-%d') = date_format(date_sub(curdate(), interval 21 day), '%Y-%m-%d') and fail_item is not null*/
                and not EXISTS(select mesNo from table4 where mesNo = A.issure_no) group by A.issure_no order by failItem
            ),
    				-- 临时表： t2表问题单明细表，右联 t1编码分配责任人表 通过编码进行关联 排查问题单表中编码为空的  然后根据编码分组  合并组内的单号，单数，父项编码，求和坏件数
    				table3 as (
                select group_concat(distinct A.mesNo) mesNo,count(distinct A.mesNo) mesCount,group_concat(distinct A.itemCode) itemCode,A.failItem,defect_name,
                A.supplier,A.instance_create_time,IFNULL(sum(A.badQty),'0') badQty,
                IFNULL(B.product,'') product,IFNULL(B.project,'') project,IFNULL(B.category,'') category,IFNULL(B.subCategory,'') subCategory,B.member,B.analysisBy,B.reviewBy
                from table2 A right join table1 B on A.failItem = B.itemCode where A.failItem is not null group by A.failItem
            ),
    				-- 临时表 将t3 表的预警表 再完善： 添加预警开始 结束时间，以及预警的类型
    				table5 AS (
                select mesNo,mesCount,itemCode,failItem,#{queryParam.startPeriod} startPeriod,#{queryParam.endPeriod} endPeriod,'不良数量预警' warnType,IFNULL(defect_name,'') warnRea,
                supplier,badQty,product,project,category,subCategory,member,analysisBy,reviewBy from table3
            ),
    				-- 临时表 单板 + 整机编码的责任人配置表
    				table6 AS (
                select A.PRODUCT_CODE itemCode,A.ANALYST analyst,A.REVIEWER reviewer, '1' codeType from txny.dwr_mt_xxx_report_attr_f A union
                select B.PCBA_CODE itemCode,group_concat(distinct A.ANALYST) ,group_concat(distinct A.REVIEWER) reviewer,'2' codeType from txny.dwr_mt_xxx_report_bord_f B
                left join txny.dwr_mt_xxx_report_attr_f A on A.PRODUCT_CODE = B.PRODUCT_CODE
                group by A.PRODUCT_SERIES,B.PCBA_CODE
            ),
    				-- 临时表 单板 + 整机编码的归属信息
    				table7 as (
                select group_concat(distinct product) product,group_concat(distinct project) project,itemCode,codeType from (
                    select A.PRODUCT_SERIES product,A.MODEL project, A.PRODUCT_CODE itemCode, '1' codeType from txny.dwr_mt_xxx_report_attr_f A union
                    select A.PRODUCT_SERIES product,A.MODEL project, B.PCBA_CODE itemCode,'2' codeType from txny.dwr_mt_xxx_report_bord_f B
                    left join txny.dwr_mt_xxx_report_attr_f A on A.PRODUCT_CODE = B.PRODUCT_CODE
                ) t group by itemCode
            )
    				
    				-- 进行并表 并且覆盖刷新产品归属信息 责任人信息
            select mesNo,mesCount,a.itemCode,failItem,startPeriod,endPeriod,warnType,warnRea,supplier,badQty,IFNULL(c.product,a.product) product,IFNULL(c.project,a.project) project,category,subCategory,
            dpDeduplicate(concat(IFNULL(b.analyst,analysisBy),',',IFNULL(reviewBy,b.reviewer))) as member,IFNULL(b.analyst,analysisBy) analysisBy,IFNULL(reviewBy,b.reviewer) reviewBy
            from table5 a left join table6 b on a.itemCode = b.itemCode left join table7 c on a.itemCode = c.itemCode

[16_Springboot_mybatisplus_ShedLock_studyday1_-CSDN]: https://blog.csdn.net/studyday1/article/details/131013663