MySQL分组用逗号分隔的字段 [英] MySQL grouping with a comma separated field

问题描述

这是一个棘手的人.

我的数据如下:

User ID  Name     Skill Sets
1        Jim      Install, Configure
2        Jack     Install
3        John     Configure, Setup, Blah
4        Bill     Setup, Install

这不是我的设计，也不是更改数据格式化方式的选项.麻烦的是我需要按独特的技能组合.显然现在有一个小组给了我:

This isn't my design, and it's not an option to change the way the data is formatted. The trouble is that I need to group by the unique skill sets. Obviously a group by right now gives me:

Skill Set                Count
Install, Configure       1
Install                  1
Configure, Setup, Blah   1
Setup, Install           1

所需的输出是:

Skill Set    Count
Install      3
Configure    2
Setup        2
Blah         1

有什么想法吗?我可以想象得出一个观点，将技能集分为规范化形式(应该如此).但是我也不赞成这种语法.

Any Ideas? I could conceivably make a view that separates the skill sets into a normalized form (as it should be). But I'm not positive on the syntax for that either.

推荐答案

您需要具有一个包含所有可能的技能值的行集.

You need to have a rowset containing all possible values of your skills.

MySQL缺乏做到这一点的方法，因此您必须以某种方式生成它.

MySQL lacks a way to do it, so you'll have to generate it somehow.

如果您有这样的结果集，只需发出:

If you have such a resultset, just issue:

SELECT  skill, COUNT(*)
FROM    (
        SELECT 'Install' AS skill
        UNION ALL
        SELECT 'Configure' AS skill
        UNION ALL
        SELECT 'Setup' AS skill
        UNION ALL
        SELECT 'Blah' AS skill
        ) s
JOIN    users u
ON      find_in_set(s.skill, u.sets)
GROUP BY
        s.skill

由于您提到自己在单独的表格中有技能，请使用该表格:

Since you mentioned that you have your skills in a separate table, use that table:

SELECT  skill, COUNT(*)
FROM    skills s
JOIN    users u
ON      find_in_set(s.skill, u.sets)
GROUP BY
        s.skill

但是，这永远不会与错别字相匹配，只会被忽略.

This, however, will not ever match the typos, they will be just skipped.

这篇关于MySQL分组用逗号分隔的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！