mysql按照特定字符拆分字段为多行和多列的产品销量拆分案例

我去查了一下订单表,需要案例数据的，关注并进入公众号后，回复“数据源”，商品运营案例表数据。

SELECT 
 user_type,
 order_products
FROM 
 `商品运营案例`
WHERE
 DATE_FORMAT(order_time,"%Y/%m")="2020/08";

复制

这是什么鬼，原来生产环境中order_products记录的是：产品编码(销量);产品编码(销量);...，那就是要把每单的产品和销量差分开来，然后再汇总。

先看怎么拆分产品和数量吧

有那么一个函数叫SUBSTRING_INDEX()

• 语法：substring_index("待截取有用部分的字符串","截取数据依据的字符",截取依据的字符的位置N[正常从前往后，若为-1则是从后往前截取依据的字符后面的内容]）

有一个mysql的存放参考文档的表mysql.help_topic

select * from mysql.help_topic

拆分产品思路

1. 按照分号拆分，有多少个分号呢，(length(a.order_products)-length(REPLACE(a.order_products,'；',''))) ，即总长度-去除分号的长度。
2. 每个分号都要拆分一次，也就是位置1拆分1次，位置2拆分1次，...,

SELECT 
 a.user_type as 客户类型,
 a.order_products as 订单产品,
 b.help_topic_id+1,
 SUBSTRING_INDEX(a.order_products, '；', b.help_topic_id+1)
FROM 
 商品运营案例 AS a
JOIN 
 mysql.help_topic AS b 
ON
 b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,'；',''))) 
WHERE 
 DATE_FORMAT(order_time,"%Y/%m")="2020/08"

复制

结果如上，为什么两个分号有6个结果呢，其实我们的一个中文状态下的分号长度是3导致。

1. 截取每个分号的最后一个产品编号和数量，这样每行就只有唯一不重复的一个产品编号和数量了，当然还有就是最后一个字符是分号的怎会产生空值，所以where条件中应该去除这部分。

SELECT 
 a.user_type as 客户类型,
 a.order_products as 订单产品,
 b.help_topic_id+1,
 SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1) AS 产品编号_num
FROM 
 商品运营案例 AS a
JOIN 
 mysql.help_topic AS b 
ON
 b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,'；',''))) 
WHERE 
 DATE_FORMAT(order_time,"%Y/%m")="2020/08"
AND 
 SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1)<>""

复制

4. 接下来就是要把小括号中的产品和数量差分为两列。其中产品编号我们再来一次SUBSTRING_INDEX(产品编号_num,"(",1);产品数量等于replace(SUBSTRING_INDEX(产品编号_num,"(",-1),")","")

拆分产品的完整代码如下

SELECT 
 a.user_type as 客户类型,
 a.order_products as 订单产品,
--  b.help_topic_id+1,
 SUBSTRING_INDEX(
  SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1)
  ,'；',-1) AS 产品编号_num,
 SUBSTRING_INDEX(
  SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1)
  ,"(",1) AS 产品编号,
 replace(
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1),"(",-1)
  ,")","") AS 产品数量
FROM 
 商品运营案例 AS a
JOIN 
 mysql.help_topic AS b 
ON
 b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,'；',''))) 
WHERE  
 SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1)<>""
AND
 DATE_FORMAT(order_time,"%Y/%m")="2020/08"

复制

接下来就是分类汇总

需求：帮我查一下新客户和老客户分别在8月份买了多少A-J产品

SELECT 
 T1.客户类型,
 COUNT(产品编号) AS 订单数,
 SUM(产品数量) AS 产品数量
FROM
(
  SELECT 
   a.user_type as 客户类型,
   a.order_products as 订单产品,
  --  b.help_topic_id+1,
   SUBSTRING_INDEX(
    SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1)
    ,'；',-1) AS 产品编号_num,
   SUBSTRING_INDEX(
    SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1)
    ,"(",1) AS 产品编号,
   replace(
    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1),"(",-1)
    ,")","") AS 产品数量
  FROM 
   商品运营案例 AS a
  JOIN 
   mysql.help_topic AS b 
  ON
   b.help_topic_id<(length(a.order_products)-length(REPLACE(a.order_products,'；',''))) 
  WHERE  
   SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1)<>""
  AND
   DATE_FORMAT(order_time,"%Y/%m")="2020/08"
  AND
   SUBSTRING_INDEX(
    SUBSTRING_INDEX(SUBSTRING_INDEX(a.order_products, '；',b.help_topic_id+1),'；',-1)
    ,"(",1) = "A-J"
) AS T1
GROUP BY 
 T1.客户类型

复制