MySQL实现一对多关系的sum求和操作（mysql 一对多sum）

MySQL实现一对多关系的sum求和操作

在MySQL中，通过一对多关系的表结构设计，可以实现多个表之间的关联查询和聚合操作。其中，sum求和操作是常见的聚合操作之一，可用于汇总某一列的值。下面将介绍如何在MySQL中实现一对多关系的sum求和操作。

1. 表结构设计

为了实现一对多关系的sum求和操作，需要设计符合规范的表结构。考虑一个简单的业务场景：商店销售统计。商店销售统计有以下三个表：

– 商品表（product），存储所有销售的商品信息。

– 订单表（order），存储所有订单信息。

– 订单商品表（order_product），用于记录一个订单中购买的多种商品信息。

其中，商品表（product）和订单表（order）为一对多关系，订单表（order）和订单商品表（order_product）也为一对多关系。表结构设计如下：

“`sql

CREATE TABLE `product` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

`price` DECIMAL(10,2) NOT NULL,

PRIMARY KEY (`id`)

);

CREATE TABLE `order` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`order_no` VARCHAR(20) NOT NULL,

`order_time` DATETIME NOT NULL,

PRIMARY KEY (`id`)

);

CREATE TABLE `order_product` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`order_id` INT(11) NOT NULL,

`product_id` INT(11) NOT NULL,

`quantity` INT(11) NOT NULL,

PRIMARY KEY (`id`),

INDEX `fk_order` (`order_id`),

INDEX `fk_product` (`product_id`),

FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

);

2. 数据录入

为了测试一对多关系的sum求和操作，需要在商品表（product）、订单表（order）和订单商品表（order_product）中录入一些测试数据。录入数据的代码如下：

```sql
-- 插入商品信息
INSERT INTO `product` (`name`, `price`)
VALUES ('iPhone XS', 9999.00), ('iPad Pro', 7999.00), ('MacBook Pro', 14999.00);
-- 插入订单信息
INSERT INTO `order` (`order_no`, `order_time`)
VALUES ('201902120001', '2019-02-12 10:00:00'), ('201902120002', '2019-02-12 14:00:00');

-- 插入订单商品信息
INSERT INTO `order_product` (`order_id`, `product_id`, `quantity`)
VALUES (1, 1, 2), (1, 2, 1), (2, 1, 1), (2, 3, 2);

3. 实现sum求和操作

通过一对多关系的表结构设计，可以实现订单商品表（order_product）中购买单个商品的数量（quantity）与商品价格（price）相乘后的总金额（total_price）的求和（sum）操作。具体实现方式如下：

“`sql

SELECT `order`.`id` AS `order_id`, `order_no`, `order_time`, SUM(`quantity` * `price`) AS `total_price`

FROM `order`

JOIN `order_product` ON `order`.`id` = `order_product`.`order_id`

JOIN `product` ON `order_product`.`product_id` = `product`.`id`

GROUP BY `order`.`id`;

上述代码中，通过JOIN子句实现了三个表的关联查询。GROUP BY子句根据订单id对总金额进行分组，进而实现sum求和操作。查询结果如下：

| order_id | order_no      | order_time          | total_price |
| -------- | -------------| -------------------| ----------- |
| 1        | 201902120001 |2019-02-12 10:00:00  | 25997.00    |
| 2        | 201902120002 |2019-02-12 14:00:00  | 28997.00    |
通过以上代码可以实现一对多关系的sum求和操作，同时可以应用于其他聚合操作（如avg、max、min等）。