我有一个表Books,其中有一列表示书籍的发行年份(类型:年)。我想算一下每十年发行的书籍数量。

我尝试了以下方法:

select count(*) as count, concat(decade, '-', decade + 9) as ReleaseYear
from (
    select floor('ReleaseYear' / 10) * 10 as decade
    from Book
) t
group by decade;


但是它返回0个结果。

当我尝试year('ReleaseYear')时,它返回空值。

最佳答案

为每行创建一个decade值,然后按十年查找count(decade)..组。

询问

select t.decade,count(t.decade) as BooksCount from
(
    select *,
    concat((releaseYear - ((releaseYear % 10)-1)),'-',((releaseYear - ((releaseYear % 10)-1))+ 9)) as decade
   from Books
)t
group by t.decade;


SQL Fiddle

关于mysql - 基于数十年的数据计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/32919788/

10-12 05:18