又到SQL。
目录
1.列出指定时间段内所有的下单产品
2.学生们参加各科测试的次数
3.即时食物配送 II
4.买下所有产品的客户
5.按分类统计薪水
1.列出指定时间段内所有的下单产品
sql"># Write your MySQL query statement below
select t1.product_name,sum(t2.unit) as unit
from Products t1
join Orders t2
on t1.product_id=t2.product_id and DATE_FORMAT(t2.order_date,'%Y-%m')='2020-02'
group by t1.product_id,t1.product_name
having unit>99;
2.学生们参加各科测试的次数
sql"># Write your MySQL query statement below
select a.student_id,a.student_name,b.subject_name,count(e.subject_name) as attended_exams
from Students a cross join Subjects bleft join Examinations e on a.student_id=e.student_id and b.subject_name=e.subject_name
group by a.student_id,b.subject_name
order by a.student_id,b.subject_name 
3.即时食物配送 II
sql"># Write your MySQL query statement below
#首先筛选出首次订单，使用min作用于用户分组
#通过where in 获取所有首次订单
select round(sum(if(order_date=customer_pref_delivery_date,1,0))*100/count(*),2) immediate_percentage
from Delivery
where (customer_id,order_date) in (select customer_id,min(order_date) from Delivery group by customer_id) 
4.买下所有产品的客户
sql"># Write your MySQL query statement below
select customer_id
from Customer
group by customer_id
having count(distinct(product_key))=(select count(*) from Product)
5.按分类统计薪水
sql"># Write your MySQL query statement below
select 'Low Salary' as category,count(*) as accounts_count from Accounts
where income<20000
union
select 'Average Salary'as category,count(*) as accounts_count from Accounts
where income between 20000 and 50000
union
select 'High Salary' as category,count(*) as accounts_count from Accounts
where income>50000 
ps:
