LeetCode-数据库题（三） (126- ? 到1607)
程序员文章站
2022-07-06 09:31:44
文章目录[1571. 仓库经理](https://leetcode-cn.com/problems/warehouse-manager/)[1581. 进店却未进行过交易的客户](https://leetcode-cn.com/problems/customer-who-visited-but-did-not-make-any-transactions/)[1587. Bank Account Summary II](https://leetcode-cn.com/problems/bank-account...
文章目录
- [1571. 仓库经理](https://leetcode-cn.com/problems/warehouse-manager/)
- [1581. 进店却未进行过交易的客户](https://leetcode-cn.com/problems/customer-who-visited-but-did-not-make-any-transactions/)
- [1587. Bank Account Summary II](https://leetcode-cn.com/problems/bank-account-summary-ii/)
- [1596. The Most Frequently Ordered Products for Each Customer](https://leetcode-cn.com/problems/the-most-frequently-ordered-products-for-each-customer/)
- [1607. Sellers With No Sales](https://leetcode-cn.com/problems/sellers-with-no-sales/)
1571. 仓库经理
难度简单
SQL架构
表: Warehouse
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| name | varchar |
| product_id | int |
| units | int |
+--------------+---------+
(name, product_id) 是该表主键.
该表的行包含了每个仓库的所有商品信息.
表: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
| Width | int |
| Length | int |
| Height | int |
+---------------+---------+
product_id 是该表主键.
该表的行包含了每件商品以英尺为单位的尺寸(宽度, 长度和高度)信息.
写一个 SQL 查询来报告, 每个仓库的存货量是多少立方英尺.
- 仓库名
- 存货量
返回结果没有顺序要求.
查询结果如下例所示.
Warehouse 表:
+------------+--------------+-------------+
| name | product_id | units |
+------------+--------------+-------------+
| LCHouse1 | 1 | 1 |
| LCHouse1 | 2 | 10 |
| LCHouse1 | 3 | 5 |
| LCHouse2 | 1 | 2 |
| LCHouse2 | 2 | 2 |
| LCHouse3 | 4 | 1 |
+------------+--------------+-------------+
Products 表:
+------------+--------------+------------+----------+-----------+
| product_id | product_name | Width | Length | Height |
+------------+--------------+------------+----------+-----------+
| 1 | LC-TV | 5 | 50 | 40 |
| 2 | LC-KeyChain | 5 | 5 | 5 |
| 3 | LC-Phone | 2 | 10 | 10 |
| 4 | LC-T-Shirt | 4 | 10 | 20 |
+------------+--------------+------------+----------+-----------+
Result 表:
+----------------+------------+
| warehouse_name | volume |
+----------------+------------+
| LCHouse1 | 12250 |
| LCHouse2 | 20250 |
| LCHouse3 | 800 |
+----------------+------------+
Id为1的商品(LC-TV)的存货量为 5x50x40 = 10000
Id为2的商品(LC-KeyChain)的存货量为 5x5x5 = 125
Id为3的商品(LC-Phone)的存货量为 2x10x10 = 200
Id为4的商品(LC-T-Shirt)的存货量为 4x10x20 = 800
仓库LCHouse1: 1个单位的LC-TV + 10个单位的LC-KeyChain + 5个单位的LC-Phone.
总存货量为: 1*10000 + 10*125 + 5*200 = 12250 立方英尺
仓库LCHouse2: 2个单位的LC-TV + 2个单位的LC-KeyChain.
总存货量为: 2*10000 + 2*125 = 20250 立方英尺
仓库LCHouse3: 1个单位的LC-T-Shirt.
总存货量为: 1*800 = 800 立方英尺.
select name warehouse_name,sum(Width*Length*Height*units) volume
from warehouse w
left join Products p
on w.product_id= p.product_id
group by w.name
1581. 进店却未进行过交易的客户
难度简单1收藏分享切换为英文接收动态反馈
SQL架构
表：Visits
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| visit_id | int |
| customer_id | int |
+-------------+---------+
visit_id是该表的主键。
该表包含有关光临过购物中心的顾客的信息。
表：Transactions
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| transaction_id | int |
| visit_id | int |
| amount | int |
+----------------+---------+
transaction_id 是此表的主键。
该表包含有关光临过购物中心的顾客的信息。
编写一个 SQL 查询来查找没有进行任何交易的访问用户的 ID ，以及他们进行这些访问的次数。
返回以任何顺序排序的结果表。
查询结果格式如下例所示：
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1 | 23 |
| 2 | 9 |
| 4 | 30 |
| 5 | 54 |
| 6 | 96 |
| 7 | 54 |
| 8 | 54 |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2 | 5 | 310 |
| 3 | 5 | 300 |
| 9 | 5 | 200 |
| 12 | 1 | 910 |
| 13 | 2 | 970 |
+----------------+----------+--------+
Result 表：
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54 | 2 |
| 30 | 1 |
| 96 | 1 |
+-------------+----------------+
ID = 23 的客户曾经逛过一次购物中心，并在 ID = 12 的访问期间进行了一笔交易。
ID = 9 的客户曾经逛过一次购物中心，并在 ID = 13 的访问期间进行了一笔交易。
ID = 30 的客户曾经去过购物中心，并且没有进行任何交易。
ID = 54 的客户三度造访了购物中心。在 2 次访问中，他们没有进行任何交易，在 1 次访问中，他们进行了 3 次交易。
ID = 96 的客户曾经去过购物中心，并且没有进行任何交易。
如我们所见，ID 为 30 和 96 的用户一次没有进行任何交易就去了购物中心。用户 54 也两次访问了购物中心并且没有进行任何交易。
select customer_id,count(*) count_no_trans
from Visits v left join Transactions t
on t.visit_id = v.visit_id
where amount is null
group by customer_id
1587. Bank Account Summary II
难度简单
SQL架构
Table: Users
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| account | int |
| name | varchar |
+--------------+---------+
account is the primary key for this table.
Each row of this table contains the account number of each user in the bank.
Table: Transactions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| trans_id | int |
| account | int |
| amount | int |
| transacted_on | date |
+---------------+---------+
trans_id is the primary key for this table.
Each row of this table contains all changes made to all accounts.
amount is positive if the user received money and negative if they transferred money.
All accounts start with a balance 0.
Write an SQL query to report the name and balance of users with a balance higher than 10000. The balance of an account is equal to the sum of the amounts of all transactions involving that account.
Return the result table in any order.
The query result format is in the following example.
Users table:
+------------+--------------+
| account | name |
+------------+--------------+
| 900001 | Alice |
| 900002 | Bob |
| 900003 | Charlie |
+------------+--------------+
Transactions table:
+------------+------------+------------+---------------+
| trans_id | account | amount | transacted_on |
+------------+------------+------------+---------------+
| 1 | 900001 | 7000 | 2020-08-01 |
| 2 | 900001 | 7000 | 2020-09-01 |
| 3 | 900001 | -3000 | 2020-09-02 |
| 4 | 900002 | 1000 | 2020-09-12 |
| 5 | 900003 | 6000 | 2020-08-07 |
| 6 | 900003 | 6000 | 2020-09-07 |
| 7 | 900003 | -4000 | 2020-09-11 |
+------------+------------+------------+---------------+
Result table:
+------------+------------+
| name | balance |
+------------+------------+
| Alice | 11000 |
+------------+------------+
Alice's balance is (7000 + 7000 - 3000) = 11000.
Bob's balance is 1000.
Charlie's balance is (6000 + 6000 - 4000) = 8000.
select name,sum(amount) balance
from Transactions t join Users u
on t.account = u.account
group by name
having balance>10000
1596. The Most Frequently Ordered Products for Each Customer
难度中等
SQL架构
Table: Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| name | varchar |
+---------------+---------+
customer_id is the primary key for this table.
This table contains information about the customers.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| customer_id | int |
| product_id | int |
+---------------+---------+
order_id is the primary key for this table.
This table contains information about the orders made by customer_id.
No customer will order the same product more than once in a single day.
Table: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
| price | int |
+---------------+---------+
product_id is the primary key for this table.
This table contains information about the products.
Write an SQL query to find the most frequently ordered product(s) for each customer.
The result table should have the product_id and product_name for each customer_id who ordered at least one order. Return the result table in any order.
The query result format is in the following example:
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 table:
+-------------+------------+--------------+
| 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) ordered the mouse three times and the keyboard one time, so the mouse is the most frquently ordered product for them.
Bob (customer 2) ordered the keyboard, the mouse, and the screen one time, so those are the most frquently ordered products for them.
Tom (customer 3) only ordered the screen (two times), so that is the most frquently ordered product for them.
Jerry (customer 4) only ordered the keyboard (one time), so that is the most frquently ordered product for them.
John (customer 5) did not order anything, so we do not include them in the result table.
SELECT customer_id, T.product_id, product_name
FROM(
SELECT customer_id, product_id,
RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(*) DESC ) AS RK
FROM Orders o
GROUP BY customer_id, product_id
) T
LEFT JOIN Products p on p.product_id = t.product_id
WHERE RK=1
1607. Sellers With No Sales
难度简单
SQL架构
Table: Customer
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| customer_name | varchar |
+---------------+---------+
customer_id is the primary key for this table.
Each row of this table contains the information of each customer in the WebStore.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| sale_date | date |
| order_cost | int |
| customer_id | int |
| seller_id | int |
+---------------+---------+
order_id is the primary key for this table.
Each row of this table contains all orders made in the webstore.
sale_date is the date when the transaction was made between the customer (customer_id) and the seller (seller_id).
Table: Seller
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| seller_id | int |
| seller_name | varchar |
+---------------+---------+
seller_id is the primary key for this table.
Each row of this table contains the information of each seller.
Write an SQL query to report the names of all sellers who did not make any sales in 2020.
Return the result table ordered by seller_name in ascending order.
The query result format is in the following example.
Customer table:
+--------------+---------------+
| customer_id | customer_name |
+--------------+---------------+
| 101 | Alice |
| 102 | Bob |
| 103 | Charlie |
+--------------+---------------+
Orders table:
+-------------+------------+--------------+-------------+-------------+
| order_id | sale_date | order_cost | customer_id | seller_id |
+-------------+------------+--------------+-------------+-------------+
| 1 | 2020-03-01 | 1500 | 101 | 1 |
| 2 | 2020-05-25 | 2400 | 102 | 2 |
| 3 | 2019-05-25 | 800 | 101 | 3 |
| 4 | 2020-09-13 | 1000 | 103 | 2 |
| 5 | 2019-02-11 | 700 | 101 | 2 |
+-------------+------------+--------------+-------------+-------------+
Seller table:
+-------------+-------------+
| seller_id | seller_name |
+-------------+-------------+
| 1 | Daniel |
| 2 | Elizabeth |
| 3 | Frank |
+-------------+-------------+
Result table:
+-------------+
| seller_name |
+-------------+
| Frank |
+-------------+
Daniel made 1 sale in March 2020.
Elizabeth made 2 sales in 2020 and 1 sale in 2019.
Frank made 1 sale in 2019 but no sales in 2020.
select seller_name
from seller
where seller_id not in
(
select seller_id
from orders
where year(sale_date)='2020'
group by seller_id
)
order by seller_name
本文地址：https://blog.csdn.net/VanasWang/article/details/109001153