题目内容

【背景】:菜鸟需要对不同仓库的存储成本和不同货物的破损率进行深入分析，以优化仓库管理和降低损失。

【原始表】

$warehouse$_$info$ (仓库信息)表:

• $warehouse$_$id$(仓库ID): 仓库的唯一标识符，$INT$

• $warehouse$_$name$(仓库名称):仓库的名称，$VARCHAR(50)$

• $location$(位置):仓库的所在位置，$VARCHAR(30)$

• $storage$_$cost$(存储成本):仓库的每月存储成本， $DECIMAL(10,2)$

$goods$_$info$(货物信息)表:

• $goods$_$id$(货物ID):货物的唯一标识符，$INT$

• $warehouse$_$id$(仓库$ID$):关联仓库信息表的仓库 $ID$，$INT$

• $goods$_$name$(货物名称):货物的名称，$VARCHAR(30)$

【要求】:查询出每个地区仓库的存储成本占全国总存成本的比例，以及每个地区所有货物的平均破损率(破货物数量/货物总数)、每个地区存储商品的总价值。结果按照仓库 $ID$ 升排列。要求查询出来的表格的字段如下：

• $location$(位置):仓库的所在位置

• proportion_of_storage_cost:该地区仓库总存储成本(和该地区有关的仓库的存储成本的总和)占所有仓库总存储成本(所有地区所有仓库的总存储成本)的比例。($round$保留$2$位小数)

• goods_damaged_rate:该地区所有货物的平均破损率。 ($round$保留$2$位小数)

• location_goods_values:地区存储商品的总价值。($round$保留$2$位小数)

【示例】

$warehouse$_$info$(仓库信息)表:

$goods$_$info$(货物信息)表:

$damaged$_$goods$(破损货物)表:

【按要求查出来的表】

【解释】

上述示例中和“北京”地区有关的仓库 $ID$有 $1、3、6$ ,

“北京” $storage$_$cost$(存储成本)是 $5000+8000+8000=21000$ ，

所有仓库存储总成本是:$5000+8000*5=45000$，

所以 proportion_of_storage_cost=$21000/45000$ 约等于 $0.47$ ;

在 $goods$_$info$ (货物信息)表中，

和“北京”有关的仓库货物有 $goods_id$ $1、3、6、7、9$ ，共 $5$ 个商品，

其中 $goods$_$id$ $1$破损了，所以破损率=$1/5$=$0.2$;

地区存储商品总价值也是看 $goods$_$info$(货物信息)表，

$goods$_$id$ $1、3、6、7、9=1000+3000+3000+2000+2000=11000$;

样例1

输入

DROP TABLE IF EXISTS warehouse_info；
DROP TABLE IF EXISTS goods_info;
DROP TABLE IF EXISTS damaged_goods;
--创建表
CREATE TABLE warehouse_info(
warehouse_info INT PRIMARY KEY,
warehouse_name VARCHAR(50),
location VARCHAR(30),
storage_cost DECIMAL(10,2)
);

CREATE TABLE goods_info(
goods_id INT PRIMARY KEY,
warehouse_id INT,
goods_name VARCHAR(30),
goods_time DATETIME(10,2)
);

CREATE TABLE damaged_goods(
damaged_id INT PRIMARY KEY,
goods_id INT
);

-- 插入数据
INSERT INTO warehouse_info(warehouse_id,warehouse_name,location,storage_cost)
VALUES(1，'仓库 A','北京',5000.00),
(2,'仓库 B','上海',8000.00),
(3,'仓库 C','北京',8000.00),
(4,'仓库 D','上海',8000.00),
(5,'仓库 E','上海',8000.00),
(6,'仓库 F','北京',8000.00);


INSERT INTO package_info (package_id,route_id,
package_type,start_time,delivery_time)
VALUES(1,1,'商品 A',1000.00),
(2,2,'商品 B',3000.00),
(3,3,'商品 B',3000.00'),
(4,4,'商品 B',3000.00),
(5,5,'商品 B',3000.00),
(6,6,'商品 B',3000.00),
(7,1,'商品 C',2000.00),
(8,2,'商品 C',2000.00),
(9,3,'商品 C',2000.00),
(10,4,'商品 C',2000.00);

INSERT INTO lost_package (lost_id, package_id)
VALUES (1,1)
(2,10);


select*from warehouse_info;
select*from goods_info;
select*from damaged_goods;

输出

location|proportion_of_storage_cost|avg_goods_condition|total_g
oods_value
北京|0.47|0.20|11000.00
上海|0.53|0.20|13000.00