是否可以将这个mysql列的结果拆分为多行

Question

我有一个包含字段composer和play_count的表。我提出这个查询是为了显示每个作曲家的play_count的总和：

select composer, sum(play_count) as total_plays
from tracks
group by composer
order by total_plays desc

结果：

composer    total_plays
Bar Fooson  1765
Foo Barson  586
Foo Barson, Bar Fooson  397

是否可以使用,作为除法器来分割composer字段，从而得到以下结果：

composer    total_plays
Bar Fooson  2162
Foo Barson  983

相反？

我尝试了很多事情，但都没有成功。

Answer 1

最好不要在一列中存储多个值。如果你知道你最多有两个值，你可以使用：

select composer, sum(play_count) as total_plays
from ((select substring_index(composer, ',', 1) as composer, play_count
from tracks
) union all
(select substring_index(composer, ',', -1) as composer, play_count
from tracks
where composer like '%,%'
)
) ct
group by composer
order by total_plays desc;

如果列表中可以有两个以上的项，则可以向子查询添加附加条件以获得附加元素。

也就是说，您可能应该将精力放在修复数据模型上。听起来您想要一个trackComposers表来跟踪给定曲目的所有作曲家。

编辑：

如果你最多有三位作曲家，你会使用：

((select substring_index(composer, ',', 1) as composer, play_count
from tracks
) union all
(select substring_index(substring_index(composer, ',', 2), ',', -1) as composer, play_count
from tracks
where composer like '%,%'
) union all
(select substring_index(substring_index(composer, ',', 3), ',', -1) as composer, play_count
from tracks
where composer like '%,%,%'
)
)

更多的作曲家会继续这种逻辑。

Answer 2

以下是一个通用解决方案，适用于MYSQL v8及更高版本。在这个解决方案中，列组合器中可以有尽可能多的逗号分隔值。查询逻辑将动态处理它，并且在查询中不需要编辑(比如添加联合之类的(

WITH RECURSIVE sequence AS (
SELECT 1 AS level
UNION ALL
SELECT level + 1 AS value
FROM sequence
WHERE sequence.level < (Select max(length(composer) - length(REPLACE(composer, ',', '')))+1 from tracks) 
)
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(table1.composer, ',', j.whichval), ',', -1)) as exploded, Sum(Playcount)
FROM tracks table1 
JOIN 
(SELECT level As whichval
FROM sequence) as j
WHERE 1 
AND j.whichval<= 1+LENGTH(table1.composer) - LENGTH(REPLACE(table1.composer,',',''))
group by TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(table1.composer, ',', j.whichval), ',', -1));

上面查询的关键逻辑是使用递归CTE生成行。生成的行数取决于分隔符的最大数量+1(在这种情况下为逗号(，这转化为列表中可能的最大值，同时考虑列组合器的所有记录中的值。通过这种方式，它避免了根据列表的长度在查询中手动添加联合的手动干预

这里有一个db fiddle链接来说明-https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6A036adb690a124cb936bef472062f0