sql练习--计算用户8月每天的练题数量

标签：-- practice question detail 练题 2021 sql date day

描述

题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。   示例：question_practice_detail

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

根据示例，你的查询应返回以下结果：

day	question_cnt
13	5
14	2
15	3
16	1
18	1

 

select extract(day from date)day,count(*)question_cnt
from question_practice_detail
where date in(
    select date from 
    question_practice_detail
    where year(date) = 2021 and month(date) = 8
)
group by day

select
    case when year(date) = 2021 and month(date) = 8
    then extract(day from date)
    else null end day,
    count(*) question_cnt
from question_practice_detail
group by day    

select extract(day from date) day,count(*)question_cnt
from question_practice_detail
where date_format(date,"%Y-%m")="2021-08"
group by day

select day(date) `day`,count(question_id) question_cnt
from question_practice_detail
where date_format(date, "%Y-%m")="2021-08"
group by day

select
day(date) as `day`,
count(question_id) question_cnt
from question_practice_detail
where month(date) = 8 and year(date) = 2021
group by date

• 限定条件：2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，
• 比如用date_format函数的date_format(date, "%Y-%m")="202108"
• 每天：按天分组group by date
• 题目数量：count(question_id)

标签：--,practice,question,detail,练题,2021,sql,date,day
From： https://www.cnblogs.com/xinger123/p/16721403.html