以 10 秒、20 秒等为一组返回标记行

Question

我有一个座位号和标记表，如下所示：

seat    marks
61      45
62      25
63      45
64      23
65      25
66      9
67      23

最大标记为 100。现在我想显示有多少候选人在 10 秒、20 秒、30 秒、....100秒

marks     candidates_count
10        1
20        4
30        0
..        ..

等等。现在我知道了

SELECT seat, marks, count(marks) as counts from <table> group by marks order by counts  desc;

或者每 10 秒、20 秒和 30 秒执行此操作

SELECT seat, marks from <table> where marks>10 and marks<=20 group by marks;

并获取我的 PHP 中返回的行数并返回结果，但这不是很优雅。必须有一种方法可以直接在MySQL中做到这一点，而无需使用MySQL循环。

Answer 1

以下绝对适合您

select (FLOOR(`marks`/ 10)+1)*10 as marks,
       count(*) as candidates_count
from <table>
group by (FLOOR(`marks`/ 10)+1)*10;

Answer 2

试试这个：

select (marks/10)*10 as marks,
       count(*) as candidates_count
from <table>
group by (marks/10)*10

Answer 3

尝试：

select ((marks/10)+1)*10, count(1) 
from tab
group by ((marks/10)+1)*10

但是如果你想看到0你必须准备预期的分数：

create table tab2
(  
  marks int
)
insert into tab2 values(10)
insert into tab2 values(20)
insert into tab2 values(30)
insert into tab2 values(40)
insert into tab2 values(50)
insert into tab2 values(60)
....
insert into tab2 values(100)

并使用right join：

select t2.marks, count(tab.marks) 
from tab
right join tab2 t2 on t2.marks = ((tab.marks/10)+1)*10
group by t2.marks
order by 1