mysql的SUBSTRING_INDEX 和GROUP_CONCAT

旧城等待， 2022-05-16 07:52 213阅读 0赞

今天在做项目中的一个小模块，就是查询所有项目的应收款。当我拿到这个模块时，并不知道该怎么做？因为刚毕业，没有做过这样的项目，但又不得不做。幸好出生在这个网络信息纷飞的年代，于是通过谷歌来查询解决方法，最终得出了自己的方法。所以，当我们拿到自己没有做过的项目，千万不要说不会做，而是通过工具得出解决方案。  
应收款放在变更表中，关联到项目财务科目的id。项目财务表存储的对应的是一期、二期、三期等应收款。每一期应收款的金额都会变更，因而，需要将某期最近的金额拿出来，然后再进行sum统计。但凡写的不对，烦请提出。解决问题的思路：

*  按项目科目的id分组
 *  取出每组当中最新的金额
 *  再进行统计
    
    #### GROUP\_CONCAT ####
    
    这个时候就用到了GROUP\_CONCAT。GROUP是组，CONCAT合并多个字符串。顾名思义，它的功能是：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

> GROUP\_CONCAT( \[DISTINCT\] 要连接的字段 \[Order BY 排序字段 ASC/DESC\] \[Separator ‘分隔符’\] )  
> \[\]中的数据表示可用可不用

如果我们没有指定分隔符的话，默认是“，”，比如：

-- 拼接的是变更表的id（用这个id来统计变更表的金额），按照时间的降序排列，以财务科目的id进行分组，
    -- 取第一条数据，这是最新的数据，可以看下图
    SELECT GROUP_CONCAT( zmc1.id ORDER BY zmc1.create_datetime DESC ) AS 财务科目id对应变更表的id, zmc1.financial_subject_id AS 财务科目id FROM zq_money_change zmc1 WHERE zmc1.is_deleted = 0 GROUP BY zmc1.financial_subject_id;

统计结果：  
![这里写图片描述][70]  
假如，我们不用GROUP\_CONCA进行分组，再看看我们的统计结果：

SELECT zmc1.id AS 财务科目id对应变更表的id, zmc1.financial_subject_id AS 财务科目id FROM zq_money_change zmc1 WHERE zmc1.is_deleted = 0 GROUP BY zmc1.financial_subject_id ORDER BY zmc1.create_datetime ASC

![这里写图片描述][70 1]  
项目财务科目对应的id有两条，而此时只使用了一条，且还是最旧的一条数据，因而，这不符合我们的需求，所以，需要用GROUP\_CONCA分组后统计。  
我们使用“<->”，来看看统计后的数据：

SELECT GROUP_CONCAT( zmc1.id ORDER BY zmc1.create_datetime DESC SEPARATOR '<->' ) AS 财务科目id对应变更表的id, zmc1.financial_subject_id AS 财务科目id FROM zq_money_change zmc1 WHERE zmc1.is_deleted = 0 GROUP BY zmc1.financial_subject_id;

![这里写图片描述][70 2]

#### SUBSTRING\_INDEX ####

我们按照时间的降序排列，以财务科目的id进行分组，并且拼接好变更表的id，怎么取出字符串中的第一个值？这个值正是我们想要的，比如变更表的中的72这个id。这时，我们就用到了SUBSTRING\_INDEX，意思是按照下标截取字符串。

> SUBSTRING\_INDEX (str,delim,count)，str:要处理的字符串、delim:分隔符、count:计数

针对上文的查询结果来看：  
str：***GROUP\_CONCAT(zmc1.id ORDER BY zmc1.create\_datetime DESC SEPARATOR ‘<->’)***  
delim：<-> 就是str中的分隔符  
count:1，只取一个数据，比如72  
以下的代码可为：

SELECT SUBSTRING_INDEX(GROUP_CONCAT( zmc1.id ORDER BY zmc1.create_datetime DESC SEPARATOR '<->' ),'<->',1) AS 财务科目id对应变更表的id, zmc1.financial_subject_id AS 财务科目id FROM zq_money_change zmc1 WHERE zmc1.is_deleted = 0 GROUP BY zmc1.financial_subject_id;

![这里写图片描述][70 3]  
如果，我们把条件改了，会怎么样呢？  
![这里写图片描述][70 4]  
如果把参数改了，会怎么样呢？  
![这里写图片描述][70 5]

#### 得出最终的结果 ####

-- 应收款
    -- 如果变更表的id在上文的临时表中存在，我们就统计改id对应的金额
    -- 这也是我为什么拼接变跟表id的原因
    -- 需要什么，拼接什么
    SELECT zmc.new_project AS 每期应收款, zmc.financial_subject_id as 财务科目的id, zmc.id as 变更表的id FROM zq_money_change zmc WHERE zmc.id IN ( SELECT SUBSTRING_INDEX( GROUP_CONCAT( zmc1.id ORDER BY zmc1.create_datetime DESC SEPARATOR '<->' ), '<->', 2 ) FROM zq_money_change zmc1 WHERE zmc1.is_deleted = 0 GROUP BY zmc1.financial_subject_id ) AND zmc.create_datetime ORDER BY zmc.id ASC;

![这里写图片描述][70 6]  
这样就好做了，我们可以得到就可以统计每期的金额：

-- 应收款
    SELECT SUM(zmc.new_project) AS 应付款 FROM zq_money_change zmc WHERE zmc.id IN ( SELECT SUBSTRING_INDEX( GROUP_CONCAT( zmc1.id ORDER BY zmc1.create_datetime DESC SEPARATOR '<->' ), '<->', 2 ) FROM zq_money_change zmc1 WHERE zmc1.is_deleted = 0 GROUP BY zmc1.financial_subject_id ) AND zmc.create_datetime ORDER BY zmc.id ASC;

![这里写图片描述][70 7]  
所以，当我们拿到新的业务时，不要过分的紧张，首先分析该业务涉及到哪些东西，然后再逐步解决难题。

***ps:越努力，越幸运***

[70]: /images/20220516/3a6d623116c7453ca5dc5a1c56aadf57.png
[70 1]: /images/20220516/d8068316f0494ae39faf6042a1e9a50f.png
[70 2]: /images/20220516/78d868870e6a4f928709f0ee8327c95b.png
[70 3]: /images/20220516/7d1631f9248849829e8f1bdfa213f114.png
[70 4]: /images/20220516/715e4677096a4da59249778abbe3c403.png
[70 5]: /images/20220516/a0a60407c2e343c096bc95e793510f8a.png
[70 6]: /images/20220516/88c581a5176a42e89de3178dea00ff27.png
[70 7]: /images/20220516/2cd902a5f2e2451aa8346f5c74f5e379.png