mysql - 选择一个值在多值列中重复的次数

Question

考虑这张表

student_name  grade
steve         a, b,d
mike          c,d,b
bob           a,d

我想写一个查询来提取我所拥有的成绩输出

a    2
b    2
c    1
d    3

我试过:

select s1.grade, count(s1.grade) from student s1, student s2
where s1.grade = s2.grade
group by s1.grade

如何做到这一点？

Answer 1

不漂亮，但这是您不想违反第一范式并拥有多值列的原因之一...

select 'a' as grade, count(*) as occurrences
from student
where grade like '%a%'

union all

select 'b' as grade, count(*) as occurrences
from student
where grade like '%b%'

union all

select 'c' as grade, count(*) as occurrences
from student
where grade like '%c%'

union all

select 'd' as grade, count(*) as occurrences
from student
where grade like '%d%'

See it in action here .

或者，如果您有像 Chris K 提议的那样的 grades 表，您可以执行如下操作:

select g.grade, count(s.student_name) as occurances
from 
  grades g
  left join student s
    on concat(',', s.grade, ',') like concat('%,', g.grade, ',%')
group by g.grade

See it in action here .