按日期分组查询

Question

我正在处理下面的用户表，其中角色=2表示用户是指导员，而角色=3表示用户是学生。

+--------+------+---------------+
|  name  | role | creation_date |
+--------+------+---------------+
| Tom    |    2 | 2020-07-01    |
| Diana  |    3 | 2020-07-01    |
| Rachel |    3 | 2020-07-01    |
| Michel |    3 | 2020-08-01    |
+--------+------+---------------+

我的目标是选择所有教师和学生的和值，按日期分组。结果应该如下所示：

+------------------+---------------+---------------+
| totalInstructors | totalStudents | creation_date |
+------------------+---------------+---------------+
|                1 |             2 | 2020-07-01    |
|                0 |             1 | 2020-08-01    |
+------------------+---------------+---------------+

在这种情况下，在2020年-07-01年，我注册了1名讲师和2名学生，在2020-08-01年度，我没有教员，我注册了1名学生。

我的问题是我在设置这个查询时遇到了困难，如果有人能帮我的话，非常感谢你！

Answer 1

您需要使用case语句进行计数，如下所示

select count(case when role=2 then 1 end) as totalInstructors 
      ,count(case when role=3 then 1 end) as totalStudents 
      ,creation_date 
  from tbl
group by creation_date

Answer 2

使用条件聚合：

SELECT
    creation_date,
    COUNT(CASE WHEN role = 2 THEN 1 END) AS totalInstructors,
    COUNT(CASE WHEN role = 3 THEN 1 END) AS totalStudents
FROM yourTable
GROUP BY
    creation_date;

​

​

​

Demo

​

Answer 3

请使用以下查询，

select 
case when role = 2 then count(1) end as totalInstructors,
case when role = 3 then count(1) end as totalStudents,
creation_date
from table_name
group by  creation_date;

可以使用COALESCE()将null替换为0。

select COALESCE(totalInstructors, 0) as totalInstructors, COALESCE(totalStudents, 0) as totalStudents,creation_date
from
(select 
case when role = 2 then count(1) end as totalInstructors,
case when role = 3 then count(1) end as totalStudents,
creation_date
from table_name
group by  creation_date) qry;