leetcode sql练习
1549. 每件商品的最新订单
难度中等
表: Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | +---------------+---------+ customer_id 是该表主键. 该表包含消费者的信息.
表: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | customer_id | int | | product_id | int | +---------------+---------+ order_id 是该表主键. 该表包含消费者customer_id产生的订单. 不会有商品被相同的用户在一天内下单超过一次.
表: Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | | price | int | +---------------+---------+ product_id 是该表主键. 该表包含所有商品的信息.
写一个SQL 语句, 找到每件商品的最新订单(可能有多个).
返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列.
查询结果格式如下例所示:
Customers+-------------+-----------+ | customer_id | name | +-------------+-----------+ | 1 | Winston | | 2 | Jonathan | | 3 | Annabelle | | 4 | Marwan | | 5 | Khaled | +-------------+-----------+Orders+----------+------------+-------------+------------+ | order_id | order_date | customer_id | product_id | +----------+------------+-------------+------------+ | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 1 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------------+Products+------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 | +------------+--------------+-------+ Result +--------------+------------+----------+------------+ | product_name | product_id | order_id | order_date | +--------------+------------+----------+------------+ | keyboard | 1 | 6 | 2020-08-01 | | keyboard | 1 | 7 | 2020-08-01 | | mouse | 2 | 8 | 2020-08-03 | | screen | 3 | 3 | 2020-08-29 | +--------------+------------+----------+------------+ keyboard 的最新订单在2020-08-01, 在这天有两次下单. mouse 的最新订单在2020-08-03, 在这天只有一次下单. screen 的最新订单在2020-08-29, 在这天只有一次下单. hard disk 没有被下单, 我们不把它包含在结果表中.
select product_name, o.product_id, order_id, order_date from orders as o join products p on p.product_id=o.product_id where (order_date, o.product_id) in (select max(order_date), product_id from orders group by product_id) order by product_name, o.product_id, order_id
1555. 银行账户概要
难度中等
用户表： Users
+--------------+---------+ | Column Name | Type | +--------------+---------+ | user_id | int | | user_name | varchar | | credit | int | +--------------+---------+ user_id 是这个表的主键。 表中的每一列包含每一个用户当前的额度信息。
交易表：Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | trans_id | int | | paid_by | int | | paid_to | int | | amount | int | | transacted_on | date | +---------------+---------+ trans_id 是这个表的主键。 表中的每一列包含银行的交易信息。 ID 为 paid_by 的用户给 ID 为 paid_to 的用户转账。
力扣银行 (LCB) 帮助程序员们完成虚拟支付。我们的银行在表 Transaction 中记录每条交易信息，我们要查询每个用户的当前余额，并检查他们是否已透支（当前额度小于 0）。
写一条 SQL 语句，查询：
user_id用户 IDuser_name用户名credit完成交易后的余额credit_limit_breached检查是否透支 （"Yes" 或 "No"）
以任意顺序返回结果表。
查询格式见如下示例：
Users表： +------------+--------------+-------------+ | user_id | user_name | credit | +------------+--------------+-------------+ | 1 | Moustafa | 100 | | 2 | Jonathan | 200 | | 3 | Winston | 10000 | | 4 | Luis | 800 | +------------+--------------+-------------+Transactions表： +------------+------------+------------+----------+---------------+ | trans_id | paid_by | paid_to | amount | transacted_on | +------------+------------+------------+----------+---------------+ | 1 | 1 | 3 | 400 | 2020-08-01 | | 2 | 3 | 2 | 500 | 2020-08-02 | | 3 | 2 | 1 | 200 | 2020-08-03 | +------------+------------+------------+----------+---------------+ 结果表： +------------+------------+------------+-----------------------+ |user_id|user_name|credit|credit_limit_breached| +------------+------------+------------+-----------------------+ | 1 | Moustafa | -100 | Yes | | 2 | Jonathan | 500 | No | | 3 | Winston | 9900 | No | | 4 | Luis | 800 | No | +------------+------------+------------+-----------------------+ Moustafa 在 "2020-08-01" 支付了 $400 并在 "2020-08-03" 收到了 $200 ，当前额度 (100 -400 +200) = -$100 Jonathan 在 "2020-08-02" 收到了 $500 并在 "2020-08-08" 支付了 $200 ，当前额度 (200 +500 -200) = $500 Winston 在 "2020-08-01" 收到了 $400 并在 "2020-08-03" 支付了 $500 ，当前额度 (10000 +400 -500) = $9900 Luis 未收到任何转账信息，额度 = $800
select a.user_id, user_name, sum(a.credit) credit, (case when sum(a.credit)< 0 then 'Yes' else 'No' end) credit_limit_breached from ( select paid_by as user_id, sum(-amount) as credit from Transactions group by paid_by union all select paid_to as user_id, sum(amount) as credit from Transactions group by paid_to union all select user_id, credit from users) a join users b on a.user_id = b.user_id group by a.user_id
# 注：
#UNION用的比较多union all是直接连接，取到得是所有值，记录可能有重复
#union 是取唯一值，记录没有重复
# 不用group by，更加简单
select a.user_id,user_name, sum(a.credit) credit,(case when sum(a.credit)< 0 then 'Yes' else 'No' end) credit_limit_breached from (select paid_by as user_id, -amount as credit from Transactions union all select paid_to as user_id, amount as credit from Transactions union all select user_id, credit from users) as a join users b on a.user_id = b.user_id group by a.user_id
1596. 每位顾客最经常订购的商品
难度中等
表：Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | +---------------+---------+ customer_id 是该表主键 该表包含所有顾客的信息
表：Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | customer_id | int | | product_id | int | +---------------+---------+ order_id 是该表主键 该表包含顾客 customer_id 的订单信息 没有顾客会在一天内订购相同的商品 多于一次
表：Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | | price | int | +---------------+---------+ product_id 是该表主键 该表包含了所有商品的信息
写一个 SQL 语句，找到每一个顾客最经常订购的商品。
结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。
返回结果 没有顺序要求。
查询结果格式如下例所示：
Customers+-------------+-------+ | customer_id | name | +-------------+-------+ | 1 | Alice | | 2 | Bob | | 3 | Tom | | 4 | Jerry | | 5 | John | +-------------+-------+Orders+----------+------------+-------------+------------+ | order_id | order_date | customer_id | product_id | +----------+------------+-------------+------------+ | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 3 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------------+Products+------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 | +------------+--------------+-------+ Result 表： +-------------+------------+--------------+ | customer_id | product_id | product_name | +-------------+------------+--------------+ | 1 | 2 | mouse | | 2 | 1 | keyboard | | 2 | 2 | mouse | | 2 | 3 | screen | | 3 | 3 | screen | | 4 | 1 | keyboard | +-------------+------------+--------------+ Alice (customer 1) 三次订购鼠标, 一次订购键盘, 所以鼠标是 Alice 最经常订购的商品. Bob (customer 2) 一次订购键盘, 一次订购鼠标, 一次订购显示器, 所以这些都是 Bob 最经常订购的商品. Tom (customer 3) 只两次订购显示器, 所以显示器是 Tom 最经常订购的商品. Jerry (customer 4) 只一次订购键盘, 所以键盘是 Jerry 最经常订购的商品. John (customer 5) 没有订购过商品, 所以我们并没有把 John 包含在结果表中.
select customer_id, a.product_id, b.product_name from (select customer_id, product_id, count(product_id) ct from orders group by customer_id, product_id) as a , products as b where a.product_id=b.product_id and (customer_id,ct) in (select customer_id, max(ct) from ( select customer_id,count(product_id) ct from orders group by customer_id, product_id ) as tmp group by customer_id)
1613. 找到遗失的ID
难度中等
表: Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | customer_name | varchar | +---------------+---------+ customer_id 是该表主键. 该表第一行包含了顾客的名字和id.
写一个 SQL 语句, 找到所有遗失的顾客id. 遗失的顾客id是指那些不在 Customers 表中, 值却处于 1 和表中最大 customer_id 之间的id.
注意: 最大的 customer_id 值不会超过 100.
返回结果按 ids 升序排列
查询结果格式如下例所示.
Customers表: +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | Alice | | 4 | Bob | | 5 | Charlie | +-------------+---------------+ Result 表: +-----+ |ids| +-----+ | 2 | | 3 | +-----+ 表中最大的customer_id是5, 所以在范围[1,5]内, ID2和3从表中遗失.
sql里用递归，第一次见
with recursive a as ( select 1 as n union all select n+1 from a where n<100 ) select n as ids from a where n not in (select customer_id from Customers) and n<=(select max(customer_id) from Customers)
