mysql一对多合成一条数据

原创

mob64ca12f6aae1 2024-09-12 04:35:20 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12f6aae1的原创作品，请联系作者获取转载授权，否则将追究法律责任

使用MySQL实现一对多数据合成的教程

在数据库设计中，一对多关系是一个非常常见的场景。比如，客户与订单之间的关系：一个客户可以有多张订单，而每张订单只属于一个客户。在这篇文章中，我们将学习如何在MySQL中实现一对多关系的数据合成，最终将数据整合成一条记录。

整体流程

在开始之前，我们需要明确整个流程。下面是实现的步骤：

步骤	描述
1. 创建数据库和表	创建示例数据库和表，设定一对多关系
2. 插入示例数据	向表中插入一些示例数据以用于测试
3. 查询合成数据	使用SQL查询将多条记录合成一条数据
4. 结果展示	输出合成后的结果

接下来，我们将详细介绍每一步需要做的事情。

步骤一：创建数据库和表

我们首先需要创建一个数据库，并在其中创建两张表。以下是SQL代码：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS shop;

-- 使用数据库
USE shop;

-- 创建客户表
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 客户ID
    name VARCHAR(100) NOT NULL           -- 客户姓名
);

-- 创建订单表
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- 订单ID
    customer_id INT NOT NULL,            -- 外键，关联客户
    product_name VARCHAR(100) NOT NULL,  -- 产品名称
    price DECIMAL(10, 2) NOT NULL,       -- 产品价格
    FOREIGN KEY (customer_id) REFERENCES customers(id)  -- 设定外键约束
);

步骤二：插入示例数据

现在，我们向这两张表中插入示例数据：

-- 向客户表插入数据
INSERT INTO customers (name) VALUES ('Alice'), ('Bob');

-- 向订单表插入数据
INSERT INTO orders (customer_id, product_name, price) VALUES 
(1, 'Laptop', 999.99),
(1, 'Mouse', 19.99),
(2, 'Keyboard', 49.99),
(2, 'Monitor', 199.99);

步骤三：查询合成数据

现在，我们可以使用SQL查询将多条记录合成一条数据。我们需要使用GROUP_CONCAT函数将同一客户的所有订单合并为一行。下面是实现这一目标的SQL代码：

SELECT 
    c.id AS customer_id,            -- 客户ID
    c.name AS customer_name,        -- 客户姓名
    GROUP_CONCAT(o.product_name SEPARATOR ', ') AS products,  -- 合并订单产品
    SUM(o.price) AS total_price     -- 计算总金额
FROM 
    customers c
LEFT JOIN 
    orders o ON c.id = o.customer_id  -- 关联客户和订单
GROUP BY 
    c.id;  -- 按客户ID分组

步骤四：结果展示

执行上述查询后，您将得到每个客户的所有订单合并为一条输出。结果可能类似于：

customer_id	customer_name	products	total_price
1	Alice	Laptop, Mouse	1019.98
2	Bob	Keyboard, Monitor	249.98

数据可视化

为了帮助理解数据的分布情况，我们可以使用饼状图和关系图。

饼状图

下面的饼状图展示了每位客户的总支出比例：

pie
    title 客户总支出比例
    "Alice": 1019.98
    "Bob": 249.98

关系图

下面的关系图展示了客户和订单之间的关系：

erDiagram
    CUSTOMERS {
        int id "主键"
        string name "客户姓名"
    }
    ORDERS {
        int id "主键"
        int customer_id "外键，关联客户"
        string product_name "产品名称"
        decimal price "产品价格"
    }
    CUSTOMERS ||--o{ ORDERS : ""