mysql 两个统计查询合并一行

原创

mob649e8161738c 2024-02-03 09:16:51 ©著作权

文章标签 MySQL 代码示例子查询 文章分类 MySQL 数据库

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

MySQL两个统计查询合并一行

在实际的数据库应用中，我们经常需要从多个表中获取数据，并进行统计分析。在MySQL中，使用两个统计查询合并一行是一种常见的操作方式。本文将介绍如何使用MySQL实现这种操作，并提供相应的代码示例。

问题背景

假设我们有两张表，一张是users表，记录了用户的基本信息，另一张是orders表，记录了用户的订单信息。我们想要统计每个用户的订单总数和订单总金额，并将结果合并到一行中。

数据库关系图

我们可以使用mermaid语法中的erDiagram标识出数据库的关系图，如下所示：

erDiagram
    users ||--o{ orders : has

上述关系图表示users表和orders表之间存在一对多的关系，即一个用户可以有多个订单。

解决方案

我们可以通过使用子查询和连接操作来实现两个统计查询的合并。

首先，我们从orders表中统计每个用户的订单总数和订单总金额：

SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id

上述查询会返回一个结果集，包含了每个用户的订单总数和订单总金额。

接下来，我们将上述查询作为子查询，并连接users表，获取每个用户的基本信息。最终，我们将这两部分的结果合并到一行中：

SELECT u.*, o.order_count, o.total_amount
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id

上述查询会返回一个结果集，包含了每个用户的基本信息、订单总数和订单总金额，并将其合并到一行中。

代码示例

下面是一个完整的代码示例，演示了如何使用MySQL实现两个统计查询的合并：

-- 建表，插入测试数据
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users (user_id, name, age) VALUES
    (1, 'John', 25),
    (2, 'Alice', 30);

INSERT INTO orders (order_id, user_id, amount) VALUES
    (1, 1, 100.00),
    (2, 1, 200.00),
    (3, 2, 150.00),
    (4, 2, 250.00);

-- 查询每个用户的订单总数和订单总金额
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

-- 将结果合并到一行中
SELECT u.*, o.order_count, o.total_amount
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id;

旅行图

我们可以使用mermaid语法中的journey标识出查询的旅行图，如下所示：

journey
    title 查询每个用户的订单总数和订单总金额
    section 统计订单信息
        SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
        FROM orders
        GROUP BY user_id
    section 合并结果
        SELECT u.*, o.order_count, o.total_amount
        FROM users u
        LEFT JOIN (
            SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
            FROM orders
            GROUP BY user_id
        ) o ON u.user_id = o.user_id

上述旅行图描述了查询每个用户的订单总数和订单总金额的过程，包括统计订单信息和将结果合并到一行中两个步骤。