MySQL联表查询多条数据汇总成一条
在实际的应用中,有时需要将多条数据通过联表查询汇总成一条数据,以满足业务需求。MySQL提供了多种方法来实现这一功能,本文将介绍一种常用的方法。
背景
假设我们有两个表:orders
和order_items
。orders
表保存了所有订单的基本信息,order_items
表保存了每个订单的商品明细信息。两个表的结构如下:
orders
表:
id | order_no | order_date |
---|---|---|
1 | 1001 | 2021-01-01 |
2 | 1002 | 2021-01-02 |
3 | 1003 | 2021-01-03 |
order_items
表:
id | order_id | product_name | quantity |
---|---|---|---|
1 | 1 | 商品A | 2 |
2 | 1 | 商品B | 3 |
3 | 2 | 商品C | 1 |
4 | 3 | 商品B | 5 |
我们的目标是通过联表查询,将每个订单的商品明细汇总成一条数据,得到下面的结果:
order_id | order_no | order_date | products |
---|---|---|---|
1 | 1001 | 2021-01-01 | 商品A(2), 商品B(3) |
2 | 1002 | 2021-01-02 | 商品C(1) |
3 | 1003 | 2021-01-03 | 商品B(5) |
方法
我们可以使用GROUP_CONCAT
函数将每个订单的商品明细合并为一条字符串,然后通过联表查询将其与orders
表关联起来。
以下是实现的SQL语句:
SELECT
o.id AS order_id,
o.order_no,
o.order_date,
GROUP_CONCAT(oi.product_name, '(', oi.quantity, ')') AS products
FROM
orders o
JOIN
order_items oi ON o.id = oi.order_id
GROUP BY
o.id, o.order_no, o.order_date
在这个查询中,我们首先将orders
表和order_items
表通过JOIN
关键字进行联表查询,然后使用GROUP_CONCAT
函数将每个订单的商品明细合并为一条字符串,并通过GROUP BY
指定按照orders
表的id
、order_no
和order_date
进行分组。
示例
让我们通过一个示例来演示这个查询的结果。
执行以下SQL语句,创建orders
表:
CREATE TABLE orders (
id INT PRIMARY KEY,
order_no VARCHAR(20),
order_date DATE
);
INSERT INTO orders (id, order_no, order_date)
VALUES (1, '1001', '2021-01-01'),
(2, '1002', '2021-01-02'),
(3, '1003', '2021-01-03');
执行以下SQL语句,创建order_items
表:
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT,
product_name VARCHAR(20),
quantity INT
);
INSERT INTO order_items (id, order_id, product_name, quantity)
VALUES (1, 1, '商品A', 2),
(2, 1, '商品B', 3),
(3, 2, '商品C', 1),
(4, 3, '商品B', 5);
执行以下SQL语句,查询并输出结果:
SELECT
o.id AS order_id,
o.order_no,
o.order_date,
GROUP_CONCAT(oi.product_name, '(', oi.quantity, ')') AS products
FROM
orders o
JOIN
order_items oi ON o.id = oi.order_id
GROUP BY
o.id, o.order_no, o.order_date;
输出结果如下:
order_id | order_no | order_date | products |
---|---|---|---|
1 | 1001 | 2021-01-01 | 商品A(2), 商品B(3) |
2 | 1002 | 2021-01-02 | 商品C(1) |
3 | 1003 |