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 |
1条答案
按热度按时间fumotvh31#
就像我说的mysql查询很复杂。。
总体思路是一个mysql数字生成器,它生成1到10000个数字,因此它支持10000个在group列中用+符号分隔的值。
加号之间有什么数据并不重要。
查询
结果
db小提琴演示