mysql 一对多,筛选成一行

原创

mob649e8157ebce 2023-07-21 02:13:06 ©著作权

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

MySQL 一对多，筛选成一行

在关系型数据库中，一对多关系是指一个实体在另一个实体中可以有多个关联。在 MySQL 中，可以使用外键来建立一对多关系。但是，当我们需要将一对多的关系展示为一行数据时，就需要进行一些特殊的处理。

什么是一对多关系？

一对多关系是指一个实体与另一个实体之间的关联，其中一个实体可以有多个关联。

举个例子，假设我们有两个表：users 和 orders。一个用户可以有多个订单，那么这就是一个典型的一对多关系。users 表中的 user_id 是主键，orders 表中的 order_id 是主键，同时还有一个 user_id 字段用于关联用户和订单。

筛选成一行数据

当我们需要将一对多的关系展示成一行数据时，通常有两种常用的方法：使用 GROUP_CONCAT 函数和使用连接查询。

使用 GROUP_CONCAT 函数可以将多个关联数据连接成一个字符串。例如，我们可以使用以下查询语句获取每个用户的订单列表：

SELECT users.user_id, users.name, GROUP_CONCAT(orders.order_id) as order_ids
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
GROUP BY users.user_id;

这样，我们就可以得到一个结果集，其中每一行代表一个用户，并且订单以逗号分隔的字符串形式存在 order_ids 字段中。

另一种方法是使用连接查询，可以在一次查询中将多个关联数据连接起来。以下是一个使用连接查询的示例：

SELECT u.user_id, u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

这样，我们将得到一个结果集，其中每一行都包含一个用户和一个订单的信息。

代码示例

假设我们有以下两个表：users 和 orders。

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users (user_id, name) VALUES (1, 'John');
INSERT INTO users (user_id, name) VALUES (2, 'Alice');
INSERT INTO users (user_id, name) VALUES (3, 'Bob');

INSERT INTO orders (order_id, user_id, product_name, price) VALUES (1, 1, 'Product A', 10.00);
INSERT INTO orders (order_id, user_id, product_name, price) VALUES (2, 1, 'Product B', 15.00);
INSERT INTO orders (order_id, user_id, product_name, price) VALUES (3, 2, 'Product C', 20.00);

使用 GROUP_CONCAT 函数进行查询：

SELECT users.user_id, users.name, GROUP_CONCAT(orders.order_id) as order_ids
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
GROUP BY users.user_id;

结果如下：

+---------+------+-----------+
| user_id | name | order_ids |
+---------+------+-----------+
|       1 | John | 1,2       |
|       2 | Alice| 3         |
|       3 | Bob  | NULL      |
+---------+------+-----------+

使用连接查询进行查询：

SELECT u.user_id, u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

结果如下：

+---------+------+----------+
| user_id | name | order_id |
+---------+------+----------+
|       1 | John | 1        |
|       1 | John | 2        |
|       2 | Alice| 3        |
|       3 | Bob  | NULL     |
+---------+------+----------+