题目: 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数,请取出相应结果
- 限定条件:2021年8月份,匹配date字段即可,匹配方法主要有三种:
(1)like语法:date like "2021-08%"
(2)year、month函数:year(date)='2021' and month(date)='08'
;
(3)date_format函数:date_format(date, '%Y-%m')='2021-08';
- 2:总用户数:count函数计数,因为用户有重复,所以需要distinct去重,即
count(distinct device_id)
- 3:总次数:count(question_id)即可
使用like
select count(distinct device_id) as did_cut,
count(question_id) as question_cut
from question_practice_detail
where date like "2021-08%"
between匹配日期
select
count(distinct device_id) as did_cnt,
count(question_id) as question_cnt
from question_practice_detail
where
date between '2021-08-01' and '2021-08-30'
month函数
select
count(DISTINCT device_id) did_cnt,
COUNT(device_id) question_cnt
FROM question_practice_detail
WHERE MONTH(date)=8
select count(distinct device_id) as did_cnt,
count(result) as question_cnt
from question_practice_detail
where year(date)=2021 and month(date) = 8