力扣sql中等篇练习(二十四)

力扣sql中等篇练习(二十四)

1 商店中每个成员的级别

1.1 题目内容

1.1.1 基本题目信息1

1.1.2 基本题目信息2

1.1.3 示例输入输出

a 示例输入

b 示例输出

1.2 示例sql语句

SELECT m.member_id,m.name,
       case when t.r is null then 'Bronze'
            when t.r>=0 AND t.r<50 then 'Silver'
            when t.r>=50 AND t.r<80 then 'Gold'
            when t.r>=80 then 'Diamond'
            end 'category'
FROM Members m
LEFT JOIN
(
    SELECT v.member_id,100*count(p.charged_amount)/count(*) r
    FROM Visits v
    LEFT JOIN Purchases p
    ON v.visit_id=p.visit_id
    GROUP BY 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 below
SELECT t2.account_id,t2.day,
       SUM(IF(t1.type='Withdraw',t1.amount*-1,t1.amount)) balance
FROM Transactions t1
INNER JOIN Transactions t2
ON t1.account_id=t2.account_id AND t2.day>=t1.day
GROUP BY t2.account_id,t2.day
ORDER BY t2.account_id asc,t2.day asc

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
(
    SELECT distinct customer_id
    FROM Orders
    WHERE order_type=0
) AND order_type=0
UNION
SELECT order_id,customer_id,order_type
FROM Orders
WHERE customer_id not in
(
    SELECT distinct customer_id
    FROM Orders
    WHERE order_type=0
)

3.3 运行截图