SQL查询按日期范围分割

Question

我想统计学生自第一次申请以来按月划分的申请数量。

假设我的表结构如下:

Student     ApplicationDate
-------     ---------------
Barry       2009-01-01
Barry       2009-01-20
Barry       2009-01-23
Barry       2009-02-01
Barry       2009-02-15
Barry       2009-03-01

我想要一些类似的东西:

Student     Month     Applications
-------     -----     ------------
Barry       1/2009    3
Barry       2/2009    2
Barry       3/2009    1

您如何在 SQL 中为所有学生、所有应用程序执行此操作？

Answer 1

SELECT 
    student, 
    DATE_FORMAT(ApplicationDate,'%m/%Y') as Month
    count(id) as Applications
from YourTable
group by ApplicationDate