59，SQL训练之，力扣，1571. 仓库经理

最新推荐文章于 2024-10-12 20:01:41 发布

凡梦_leo

最新推荐文章于 2024-10-12 20:01:41 发布

阅读量920

点赞数 21

分类专栏： SQL实战训练文章标签： leetcode 算法爬虫后端数据仓库前端 sql

本文链接：https://blog.csdn.net/qq_55006020/article/details/140913369

版权

SQL实战训练专栏收录该内容

127 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表: 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 是该表主键(具有唯一值的列).
该表的行包含了每件商品以英尺为单位的尺寸(宽度, 长度和高度)信息.

编写解决方案报告每个仓库的存货量是多少立方英尺。

返回结果没有顺序要求。

返回结果格式如下例所示。

示例 1:

输入：
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        |
+------------+--------------+------------+----------+-----------+
输出：
+----------------+------------+
| warehouse_name | volume     | 
+----------------+------------+
| LCHouse1       | 12250      | 
| LCHouse2       | 20250      |
| LCHouse3       | 800        |
+----------------+------------+

三，建表语句

Create table If Not Exists Warehouse (name varchar(50), product_id int, units int);
Create table If Not Exists Products (product_id int, product_name varchar(50), Width int,Length int,Height int);
Truncate table Warehouse;
insert into Warehouse (name, product_id, units) values ('LCHouse1', '1', '1');
insert into Warehouse (name, product_id, units) values ('LCHouse1', '2', '10');
insert into Warehouse (name, product_id, units) values ('LCHouse1', '3', '5');
insert into Warehouse (name, product_id, units) values ('LCHouse2', '1', '2');
insert into Warehouse (name, product_id, units) values ('LCHouse2', '2', '2');
insert into Warehouse (name, product_id, units) values ('LCHouse3', '4', '1');
Truncate table Products;
insert into Products (product_id, product_name, Width, Length, Height) values ('1', 'LC-TV', '5', '50', '40');
insert into Products (product_id, product_name, Width, Length, Height) values ('2', 'LC-KeyChain', '5', '5', '5');
insert into Products (product_id, product_name, Width, Length, Height) values ('3', 'LC-Phone', '2', '10', '10');
insert into Products (product_id, product_name, Width, Length, Height) values ('4', 'LC-T-Shirt', '4', '10', '20');

四，分析

题解：

第一张表：仓库表

仓库名称，产品id，产品库存

第二章表：产品表

字段：产品id，产品名称，产品宽度，产品长度，产品高度

求，每个仓库产品存活量占据多少立方

解答：

第一步，查询产品表，开一个窗户，计算每个产品的立方长*高*宽；

第二步，以仓库表内连接产品表然后再开一列计算每个产品立方*数量

第三步，分组聚合sum 没个仓库的总占据立方

五，SQL解答

with t1 as (
    select product_id, product_name, Width, Length, Height,
       Width*Length*Height as pow
       from products
),t2 as (select name, units, pow
              , units * pow as pow2
         from warehouse w join t1
         where w.product_id = t1.product_id
)
select name as warehouse_name ,sum(pow2) as volume from t2 group by name;