MySQL基础练手题（涉及聚合函数、分组查询、时间函数）

一、题目：每位教师所教授的科目种类的数量

本题主要考验聚合函数count（）求和以及分组查询。

1 准备工作

Create table If Not Exists Teacher (teacher_id int, subject_id int, dept_id int);
Truncate table Teacher;
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '2', '3');
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '2', '4');
insert into Teacher (teacher_id, subject_id, dept_id) values ('1', '3', '3');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '1', '1');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '2', '1');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '3', '1');
insert into Teacher (teacher_id, subject_id, dept_id) values ('2', '4', '1');

查询每位老师在大学里教授的科目种类的数量；

示例 1：

输入：

Teacher 表：

输出： 

2 分析

3 实现 

select teacher_id,     
       count(distinct subject_id) cnt   -- 对科目进行求和去重，起个别名 cnmt
from teacher 
group by teacher_id;                    -- 对教师进行分组

二 、查询近30天活跃用户数

本题主要考验

• 分组查询

• 字符串函数count统计人数

• 时间函数定义时间

1 准备工作

Create table If Not Exists Activity (user_id int, session_id int, activity_date date, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'));
Truncate table Activity;
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'scroll_down');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-20', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'send_message');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'end_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'open_session');
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'end_session');

统计截至 2019-07-27（包含07-27），近30天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）

示例 1 ：

输入：

输出：

2 分析 

首先需要查询的字段为时间和用户登录数，对时间分组并且对用户人数统计查询出每日活跃的用户。（含重复id）

对用户的id进行去重查出每日活跃用户

3 实现 

select
    activity_date day,
    count(distinct user_id) active_users
from activity
where datediff('2019-07-27',activity_date) between 0 and 29
group by activity_date;