题解 | #计算用户8月每天的练题数量#
计算用户8月每天的练题数量
https://www.nowcoder.com/practice/847373e2fe8d47b4a2c294bdb5bda8b6
整理了五种解题方法
# 当作日期处理 # 1 SELECT DAY(date) as day,count(device_id) as question_cnt FROM question_practice_detail WHERE DATE_FORMAT(date,'%Y-%m') = '2021-08' group by DAY(date) # #2 SELECT DAY(date) as day,count(device_id) as question_cnt FROM question_practice_detail WHERE YEAR(date) = '2021' and MONTH(date) = '8' group by DAY(date) # # 当做字符串处理 # # 1 SELECT DAY(date) as day,count(device_id) as question_cnt FROM question_practice_detail where substring_index(date,'-',2) = '2021-08' group by DAY(date) # # 2 SELECT DAY(date) as day,count(device_id) as question_cnt FROM question_practice_detail where LEFT(date,7) = '2021-08' group by DAY(date) # 3 SELECT DAY(date) as day,count(device_id) as question_cnt FROM question_practice_detail where date regexp('^2021-08') group by DAY(date)