mysql - 对某个范围内的数据进行排序 MySQL

Question

我有一个如下表

SUBJECT  - MARKS - SEMESTER
MATH     - 50    -  1
SCIENCE  - 60    -  1
ENGLISH  - 70    -  1
MATH     - 60    -  2
SCIENCE  - 80    -  2
ENGLISH  - 90    -  2

我想产生如下所示的输出。问题是，即使没有 0-10 范围内的数据，我也希望所有三列都为 0。我无法使用“group by”和“sum”来实现。你们有什么想法

RANGE  MATH SCIENCE ENGLISH
0-10      0     0       0
10-20     0     0       0
20-30     0     0       0
30-40     0     0       0
40-50     0     0       0
50-60     1     0       0
60-70     1     1       0
70-80     0     0       1
80-90     0     1       0
90-100    0     0       1

Answer 1

您可以执行此操作，但需要定义范围，作为引用表或在查询中。剩下的就是条件聚合:

select r.range,
       sum(subject = 'MATH' and t.marks is not null) as Math,
       sum(subject = 'SCIENCE' and t.marks is not null) as Science,
       sum(subject = 'English' and t.marks is not null) as English
from ((select 0 as mins, 9.99 as maxs, '0-10' as range) union all
      (select 10 as mins, 19.99 as maxs, '10-20' as range) union all
      . . .
      (select 90 as mins, 100 as maxs, '90-100' as range)
     ) left join
     table t
     on t.marks between r.mins and r.maxs
group by r.range
order by min(r.mins);