2020-11-17

最新推荐文章于 2024-10-18 20:50:24 发布

yasin_t

最新推荐文章于 2024-10-18 20:50:24 发布

阅读量97

点赞数

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/yasin_t/article/details/109747514

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

#Mysql
场景: sql有一些字段数据是通过GROUP_CONCAT 函数合并数据,业务中会有统计合并的数据个数或者长度,这时可能想到直接在代码中循环统计，这样加大了业务返回数据的时间，我们可通过CONVERT函数在SQL语句中进行统计然后返回业务层，下面是SQL案例：

SELECT
   GROUP_CONCAT( DISTINCT details.inv_num ) AS invNum, //合并的数据
   CONVERT (
   IFNULL(
   (
   CHAR_LENGTH( GROUP_CONCAT( DISTINCT details.inv_num ) ) - CHAR_LENGTH( REPLACE ( GROUP_CONCAT( DISTINCT details.inv_num ), ',', '' ) ) 
   ) / CHAR_LENGTH( ',' ) + 1,
   0 
   ),
   SIGNED 
   ) AS invNum1  //统计的数据
FROM
   fp_account_payable AS payable
   LEFT JOIN bs_ekko AS ekko ON payable.ebeln = ekko.ebeln
   LEFT JOIN ( SELECT ebeln, post1 FROM bs_ekpo WHERE post1 != '' AND post1 IS NOT NULL GROUP BY ebeln ) AS ekpo ON payable.ebeln = ekpo.ebeln
   LEFT JOIN mm_erp_plant AS plant ON plant.plant_id = ekko.werks
   LEFT JOIN fp_invoice_details AS details ON payable.ebeln = details.ebeln 
GROUP BY
   payable.ebeln 
ORDER BY
   payable.handover_time DESC
   
   
```![查询案例](https://img-blog.csdnimg.cn/20201117172036151.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhc2luX3Q=,size_16,color_FFFFFF,t_70#pic_center)