力扣1113(MySQL)-报告的记录(简单)
题目:
动作表:Actions
此表没有主键,所以可能会有重复的行。
action 字段是 ENUM 类型的,包含:('view', 'like', 'reaction', 'comment', 'report', 'share')
extra 字段是可选的信息(可能为 null),
其中的信息例如有:1.报告理由(a reason for report) 2.反应类型(a type of reaction)
编写一条SQL,查询每种 报告理由(report reason)在昨天的报告数量。
假设今天是 2019-07-05。
查询及结果的格式示例:
建表语句:
1 Create table If Not Exists actions_1113 ( 2 user_id int, 3 post_id int, 4 action_date date, 5 action enum('view', 'like', 'reaction', 'comment', 'report', 'share'), 6 extra varchar(6) 7 ); 8 Truncate table actions_1113; 9 insert into actions_1113 (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01','view',null),('1', '1', '2019-07-01','like',null),('1', '1', '2019-07-01','share',null),('2', '4', '2019-07-04','view',null),('2', '4', '2019-07-04','report','spam'),('3', '4', '2019-07-04','view',null),('3', '4', '2019-07-04','report','spam'),('4', '3', '2019-07-02','view',null),('4', '3', '2019-07-02','report','spam'),('5', '2', '2019-07-04','view',null),('5', '2', '2019-07-04','report','racism'),('5', '5', '2019-07-04','view',null),('5', '5', '2019-07-04','report','racism');
解题思路:
筛选条件为:action要为report且日期为2019-07-05的前一天,然后以extra分组统计出post_id的数量,注意使用distinct
select extra as report_reason,count(distinct post_id) as report_count from actions_1113 where action = 'report' and action_date = '2019-07-04' group by extra;