将带有"+"分隔符的字符串拆分为单独的行并应用聚合

Question

数据不是静态的，用 + 分隔的字符组可能会有所不同。我希望所有由 + 分隔的字符都按行排列，然后在其顶部应用聚合。我在窗口中使用 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   

Answer 1

就像我说的MySQL查询很复杂..
一般的想法是一个MySQL数字生成器，它生成1到10000，因此它支持10000个带有组列中+号的分隔值。
+ 号之间的数据并不重要。

查询

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小提琴演示