选择一个值是多少时，重复多值列内

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。

或者，如果你有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。

Answer 2

或者，如果你有一个包含可能的等级列表的表（称为grades）：

grade 
----- 
a 
b 
c 
d 
e 

然后下面的语句也将工作：

select g.grade as [Grade], (select count(1) from student where grade like '%'+g.grade+'%') as [Count] from grades g order by g.grade asc 

这也许是更灵活将其他潜在等级添加到计数中的条款。

但正如上面所说的......避开后果自负正常化......