mysql - GROUP BY 逗号分隔列表的成员

Question

我有一个数据表如下:

t1
____________________________________________________
resources          | companyName | Response | Score 
David, Matt        |  companyA   |  YES     |   5
Matt               |  compqanyB  |  NO      |   8
Kyle, Kyle, David  |  companyC   |  YES     |   2

如您所见，resources 是一个逗号分隔的字符串。此外，并非 resources 的所有成员都必须是唯一的(参见第 3 行)。

我想GROUP BY 任何列表中可用的每个DISTINCT 成员。将聚合所有其他列。

预期结果:

query
______________________________________________________________________________
resources     |  companyName        |    Response         | Score
David         | *agregated result*  | *agregated result*  | *agregated result*  
Matt          | *agregated result*  | *agregated result*  | *agregated result*  
Kyle          | *agregated result*  | *agregated result*  | *agregated result*  

编辑:

另一种可能性:

query
____________________________________________________
resources          | companyName | Response | Score 
David              |  companyA   |  YES     |   5
Matt               |  companyA   |  YES     |   5
Matt               |  compqanyB  |  NO      |   8
Kyle               |  companyC   |  YES     |   2
David              |  companyC   |  YES     |   2

Answer 1

让我假设您有一个包含各个名称的资源表。在这种情况下，您可以使用以下查询执行您想要的操作:

select r.name, <other aggregated results>
from t1 join
     Resources r
     on concat(', ', t1.resources, ', ') like concat(', %', r.name, ', %')
group by r.name

如果您没有资源表，您可能应该有。将此类内容存储在逗号分隔列表中通常不是一个好主意。如果数据未规范化并且会出现拼写错误，那将是一个特别糟糕的主意。