使用“+”分隔符将字符串拆分为单独的行并应用聚合

pftdvrlh  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(384)

数据不是静态的,由+分隔的字符组可能会有所不同。我希望所有由+分隔的字符按行排列,然后在其顶部应用聚合。我在windows中使用mysql 5.7.14。假设数据为:

group   val
a+b     10
a       5
b       6
b+d+c   12
d       13
c+d     12

输出应如下所示:

grp_item val
a        15
b        28
c        24
d        24
fumotvh3

fumotvh31#

就像我说的mysql查询很复杂。。
总体思路是一个mysql数字生成器,它生成1到10000个数字,因此它支持10000个在group列中用+符号分隔的值。
加号之间有什么数据并不重要。
查询

SELECT 
   Table1_unique_groups.`group`
 , SUM(Table1.val)
FROM (

SELECT 
 DISTINCT
   SUBSTRING_INDEX(SUBSTRING_INDEX(Table1.`group`, '+', number_generator.number), '+', -1) AS `group`

FROM (
  SELECT 
   @row := @row + 1 AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) record_1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) record_2    
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) record_4
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) record_5     
  CROSS JOIN (
    SELECT @row := 0 
  ) AS init_user_params
) AS number_generator
CROSS JOIN 
 Table1 

) AS Table1_unique_groups

INNER JOIN 
  Table1
ON
  FIND_IN_SET(Table1_unique_groups.`group`, REPLACE(Table1.group, '+', ','))

GROUP BY 
 Table1_unique_groups.`group`

结果

| group | SUM(Table1.val) |
| ----- | --------------- |
| a     | 15              |
| b     | 28              |
| c     | 24              |
| d     | 37              |

db小提琴演示

相关问题