SELECT m.member_id,m.name,casewhen t.r isnullthen'Bronze'when t.r>=0AND t.r<50then'Silver'when t.r>=50AND t.r<80then'Gold'when t.r>=80then'Diamond'end'category'FROM Members m
LEFTJOIN(SELECT v.member_id,100*count(p.charged_amount)/count(*) r
FROM Visits v
LEFTJOIN Purchases p
ON v.visit_id=p.visit_id
GROUPBY v.member_id
)t
ON m.member_id=t.member_id
1.3 运行截图
2 账户余额
2.1 题目内容
2.1.1 基本题目信息
2.1.2 示例输入输出
2.2 示例sql语句
# Write your MySQL query statement belowSELECT t2.account_id,t2.day,SUM(IF(t1.type='Withdraw',t1.amount*-1,t1.amount)) balance
FROMTransactions t1
INNERJOINTransactions t2
ON t1.account_id=t2.account_id AND t2.day>=t1.dayGROUPBY t2.account_id,t2.dayORDERBY t2.account_id asc,t2.dayasc
2.3 运行截图
3 为订单类型为0的客户删除类型为1的订单
3.1 题目内容
3.1.1 基本题目信息
3.1.2 示例输入输出
3.2 示例sql语句
# ①分别找到订单类型为0的顾客id和没有订单类型为0的顾客id# ②按照规则找出其对应的订单# ③汇总即可SELECT order_id,customer_id,order_type
FROM Orders
WHERE customer_id in(SELECTdistinct customer_id
FROM Orders
WHERE order_type=0)AND order_type=0UNIONSELECT order_id,customer_id,order_type
FROM Orders
WHERE customer_id notin(SELECTdistinct customer_id
FROM Orders
WHERE order_type=0)