150，SQL训练之，力扣，1677. 发票中的产品金额

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

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

Product 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| name        | varchar |
+-------------+---------+
product_id 是这张表的主键
表中含有产品 id 、产品名称。产品名称都是小写的英文字母，产品名称都是唯一的

Invoice 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| invoice_id  | int  |
| product_id  | int  |
| rest        | int  |
| paid        | int  |
| canceled    | int  |
| refunded    | int  |
+-------------+------+
invoice_id 发票 id ，是这张表的主键
product_id 产品 id
rest 应缴款项
paid 已支付金额
canceled 已取消金额
refunded 已退款金额

要求写一个SQL查询，对于所有产品，返回每个产品的产品名称，以及全部发票累计的总应缴款项、总已支付金额、总已取消金额、总已退款金额。

查询结果按 product_name 排序

示例：

Product 表：
+------------+-------+
| product_id | name  |
+------------+-------+
| 0          | ham   |
| 1          | bacon |
+------------+-------+
Invoice table:
+------------+------------+------+------+----------+----------+
| invoice_id | product_id | rest | paid | canceled | refunded |
+------------+------------+------+------+----------+----------+
| 23         | 0          | 2    | 0    | 5        | 0        |
| 12         | 0          | 0    | 4    | 0        | 3        |
| 1          | 1          | 1    | 1    | 0        | 1        |
| 2          | 1          | 1    | 0    | 1        | 1        |
| 3          | 1          | 0    | 1    | 1        | 1        |
| 4          | 1          | 1    | 1    | 1        | 0        |
+------------+------------+------+------+----------+----------+
Result 表：
+-------+------+------+----------+----------+
| name  | rest | paid | canceled | refunded |
+-------+------+------+----------+----------+
| bacon | 3    | 3    | 3        | 3        |
| ham   | 2    | 4    | 5        | 3        |
+-------+------+------+----------+----------+
- bacon 的总应缴款项为 1 + 1 + 0 + 1 = 3
- bacon 的总已支付金额为 1 + 0 + 1 + 1 = 3
- bacon 的总已取消金额为 0 + 1 + 1 + 1 = 3
- bacon 的总已退款金额为 1 + 1 + 1 + 0 = 3
- ham 的总应缴款项为 2 + 0 = 2
- ham 的总已支付金额为 0 + 4 = 4
- ham 的总已取消金额为 5 + 0 = 5
- ham 的总已退款金额为 0 + 3 = 3

三，建表语句

Create table If Not Exists Product(product_id int, name varchar(15));
Create table If Not Exists Invoice(invoice_id int,product_id int,rest int, paid int, canceled int, refunded int);
Truncate table Product;
insert into Product (product_id, name) values ('0', 'ham');
insert into Product (product_id, name) values ('1', 'bacon');
Truncate table Invoice;
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('23', '0', '2', '0', '5', '0');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('12', '0', '0', '4', '0', '3');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('1', '1', '1', '1', '0', '1');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('2', '1', '1', '0', '1', '1');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('3', '1', '0', '1', '1', '1');
insert into Invoice (invoice_id, product_id, rest, paid, canceled, refunded) values ('4', '1', '1', '1', '1', '0');

select * from product;
select * from invoice;

四，分析

思路

表格大法

第一步 左连接 用商品名称表左连接发票表

第二步 以产品名称分组 sum聚合总应缴款项、总已支付金额、总已取消金额、总已退款金额。

第三步：null值处理 排序 输出

解题过程

mysql+pandas实现逻辑

第一步 左连接 用商品名称表左连接发票表

在mysql

在pandas

第二步 以产品名称分组 sum聚合总应缴款项、总已支付金额、总已取消金额、总已退款金额。

在mysql

在pandas

第三步：null值处理 排序 输出

在mysql

在pandas

五，SQL解答

select
    p.name as name,
    ifnull(sum(rest),0) as rest,
    ifnull(sum(paid),0) as paid,
    ifnull(sum(canceled),0) as canceled,
    ifnull(sum(refunded),0) as refunded
    from Product p left join  invoice i on i.product_id=p.product_id group by p.name order by name;

六，验证

七，知识点总结

• 左连接的运用
• ifnull空值判定的运用
• 分组聚合的运用
• 排序的运用

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