SELECT extra report_reason,count(distinct post_id) report_count
FROM Actions
WHERE action_date=DATE_SUB('2019-07-05',interval1day)AND extra ISNOTNULLANDaction='report'GROUPBY extra
1.3 运行截图
2 即时事物配送|
2.1.1 基本题目信息
2.1.2 示例输入输出
2.2 示例sql语句
# 总订单数量可以在SELECT子句中用子查询查询出来SELECTROUND(count(delivery_id)/(SELECTcount(delivery_id)FROM Delivery)*100,2) immediate_percentage
FROM Delivery
WHERE order_date=customer_pref_delivery_date
2.3 运行截图
3 重新格式化部门表
3.1 题目内容
3.1.1 基本题目信息
3.1.2 示例输入输出
3.2 示例sql语句
# 多列变成多行 聚合函数加上group by处理#存在可能id值的某些月份的收入为null,这个需要考虑一下的SELECT id,sum(IF(month='Jan',revenue,null)) Jan_Revenue,sum(IF(month='Feb',revenue,null)) Feb_Revenue,sum(IF(month='Mar',revenue,null)) Mar_Revenue,sum(IF(month='Apr',revenue,null)) Apr_Revenue,sum(IF(month='May',revenue,null)) May_Revenue,sum(IF(month='Jun',revenue,null)) Jun_Revenue,sum(IF(month='Jul',revenue,null)) Jul_Revenue,sum(IF(month='Aug',revenue,null)) Aug_Revenue,sum(IF(month='Sep',revenue,null)) Sep_Revenue,sum(IF(month='Oct',revenue,null)) Oct_Revenue,sum(IF(month='Nov',revenue,null)) Nov_Revenue,sum(IF(month='Dec',revenue,null)) Dec_Revenue
FROM Department
GROUPBY id