首页 文章

MySQL在几列中计数值

提问于
浏览 次
0

我在MySQL中有一个包含学生成绩的表 . 我想发出一个SQL查询来计算所有列中的特定等级，例如

adNumber |Eng | Kis| Math| Phys| chem
1254     | A  | B  | B   | A   | D
2147     | B  | A  | A   | A   | C
3241     | D  | A  | C   | A   | E

我想得到如下内容：

Eng| kIS| Math | Phys | chem
1  | 2  |  1   |   3  |  0

当我发出声明来计算所有列中的“A”时 . 我将不胜感激任何帮助或建议 .

mysql

3 回答

  • 2

    你可以使用case或者if和one select

    select sum(case when Eng  = 'A' then 1 else 0 end) as Eng
        , sum(case when Kis  = 'A' then 1 else 0 end) as Kis
        , sum(case when Math  = 'A' then 1 else 0 end) as Math
        , sum(case when Phys  = 'A' then 1 else 0 end) as Phys
        , sum(case when Chem  = 'A' then 1 else 0 end) as Chem
    from Grades
    回复于
  • 0

    试试这个查询：

    select sum(case when Eng = 'A' then 1 else 0 end) Eng,
       sum(case when Kis = 'A' then 1 else 0 end) Kis,
       sum(case when Math = 'A' then 1 else 0 end) Math,
       sum(case when Phys = 'A' then 1 else 0 end) Phys,
       sum(case when chem = 'A' then 1 else 0 end) chem
from Grades
    回复于
  • 0

    您可以使用子查询执行此操作

    select 
    (Select count(*) from Grades where Eng = 'A') as Eng,
    (Select count(*) from Grades where Kis = 'A') as Kis,
    (Select count(*) from Grades where Math = 'A') as Math,
    (Select count(*) from Grades where Phys = 'A') as Phys,
    (Select count(*) from Grades where Chem = 'A') as Chem
    回复于

相关问题