mysql 主子表求和比较

原创

mob64ca12dd455e 2024-12-09 05:27:10 ©著作权

文章标签 数据 MySQL 表结构 文章分类 MySQL 数据库

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

MySQL 主子表求和比较

在数据库中，主子表结构是一种常见的设计模式。主表包含主记录，子表则用来存储与主记录相关联的其他信息。本文将通过一个具体示例，说明如何在 MySQL 中对主子表进行求和比较，及其在实际应用中的重要性。

1. 主子表结构

假设我们有一个销售系统，其中有两个表：orders（主表）和 order_items（子表）。orders 表包含每个订单的基本信息，而 order_items 存储与每个订单相关的具体商品信息。

表结构设计

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

在这个设计中，orders表的order_id是主键，order_items表的order_id是外键，用于连接主表和子表。

2. 示例数据

为方便讲解，我们插入一些示例数据。

INSERT INTO orders (customer_name, order_date) VALUES 
('Alice', '2023-01-10'),
('Bob', '2023-01-11'),
('Charlie', '2023-01-12');

INSERT INTO order_items (order_id, product_name, quantity, price) VALUES 
(1, 'Laptop', 1, 1500.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 100.00),
(3, 'Monitor', 1, 300.00),
(3, 'Desk', 1, 200.00);

以上数据创建了三条订单及每条订单相关的商品信息。

3. 求和比较

我们希望比较每个订单的总金额，检查是否超出某个金额阈值。可以使用 SQL 的 GROUP BY 和 SUM 函数来实现。

查询订单总金额

以下 SQL 查询将计算每个订单的总金额，并显示在总金额超过 $500 的订单。

SELECT 
    o.order_id,
    o.customer_name,
    SUM(oi.quantity * oi.price) AS total_amount
FROM 
    orders o
JOIN 
    order_items oi ON o.order_id = oi.order_id
GROUP BY 
    o.order_id, o.customer_name
HAVING 
    total_amount > 500;

结果分析

执行以上查询后，我们的结果可能如下：

order_id	customer_name	total_amount
1	Alice	1550.00
3	Charlie	500.00

从结果可以看出，只有 Alice 和 Charlie 的订单总额超过了 $500。这在实际业务中是否符合预期可以进一步分析。

4. 可视化数据

对于上面的数据，我们可以使用饼状图和序列图来帮助我们更好地理解数据。

饼状图

我们可以创建一个饼状图来展示每个顾客的订单所占的比例，如下图所示：

pie
    title Order Total Amount Distribution
    "Alice": 1550
    "Bob": 100
    "Charlie": 500

序列图

接下来，我们使用序列图展示订单的处理过程。如下所示：

sequenceDiagram
    participant Customer as 顾客
    participant Sales as 销售员
    participant Product as 产品系统

    Customer->>Sales: 下订单
    Sales->>Product: 确认商品
    Product-->>Sales: 商品确认
    Sales-->>Customer: 确认订单

在图中，顾客通过销售员下订单，销售员查询商品系统以确认商品，最终完成订单的确认。