当前位置：极客笔记 > MySQL > MySQL 问答 > MySQL多行合并成一行

MySQL多行合并成一行

MySQL多行合并成一行

MySQL多行合并成一行

前言

在使用MySQL数据库时，经常会遇到需要将多行数据合并成一行的情况。这种操作可以通过MySQL的聚合函数和GROUP BY语句来实现。本文将详细介绍在MySQL中将多行数据合并成一行的各种方法及其实现原理。

方法一：使用GROUP_CONCAT函数

MySQL提供了GROUP_CONCAT()函数，可以将每个组中的多个值连接成一个字符串，实现多行合并为一行的效果。

基本语法

SELECT column_name, GROUP_CONCAT(expression)
FROM table_name
GROUP BY column_name;
  • column_name是需要进行分组的列名，对应于GROUP BY语句中的列名。
  • expression是需要连接的列名或表达式。

例如，我们有一张名为orders的表，其中存储了客户的订单信息，包括订单号、客户姓名和产品名称。

订单表（orders）：

order_id customer product
1 John Smith iPhone
2 John Smith iPad
3 Mary Brown MacBook
4 Mary Brown iMac

我们可以使用以下语句将订单表中的每个客户的所有产品名称合并为一行：

SELECT customer, GROUP_CONCAT(product) AS products
FROM orders
GROUP BY customer;

输出：

customer products
John Smith iPhone,iPad
Mary Brown MacBook,iMac

功能扩展

控制连接字符串

GROUP_CONCAT函数默认使用逗号（,）作为连接字符串，但是我们可以通过设置SEPARATOR选项来修改连接字符串。

SELECT customer, GROUP_CONCAT(product SEPARATOR ';') AS products
FROM orders
GROUP BY customer;

输出：

customer products
John Smith iPhone;iPad
Mary Brown MacBook;iMac

对连接结果排序

GROUP_CONCAT函数默认按照插入顺序连接多个值，但是我们可以通过设置ORDER BY子句来实现对连接结果的排序。

SELECT customer, GROUP_CONCAT(product ORDER BY product DESC) AS products
FROM orders
GROUP BY customer;

输出：

customer products
John Smith iPad,iPhone
Mary Brown iMac,MacBook

方法二：使用子查询和CONCAT函数

除了使用GROUP_CONCAT函数以外，我们还可以使用子查询和CONCAT函数来实现将多行数据合并成一行的效果。

基本语法

SELECT column_name, CONCAT(expression1, expression2, ...) AS merged
FROM (
    SELECT column_name, expression
    FROM table_name
    GROUP BY column_name
) AS subquery;
  • column_name是需要进行分组的列名，对应于子查询中的列名。
  • expression是需要合并的列名或表达式。

以上文的订单表（orders）为例，我们可以使用以下语句将订单表中的每个客户的所有产品名称合并为一行：

SELECT customer, CONCAT(
        (SELECT product FROM orders WHERE customer = o.customer LIMIT 1),
        ' ',
        (SELECT product FROM orders WHERE customer = o.customer LIMIT 1,1)
    ) AS products
FROM (
    SELECT DISTINCT customer
    FROM orders
) AS o;

输出：

customer products
John Smith iPhone iPad
Mary Brown MacBook iMac

方法三：使用CASE语句和自定义变量

除了使用内置函数以外，我们还可以使用CASE语句和自定义变量来实现将多行数据合并成一行的效果。

基本语法

SET @var_name := initial_value;

SELECT column_name, CONCAT(
        MAX(CASE WHEN expression1 THEN expression2 ELSE '' END),
        MAX(CASE WHEN expression3 THEN expression4 ELSE '' END),
        ...
    ) AS merged
FROM table_name
GROUP BY column_name;
  • @var_name是自定义变量的名称。
  • initial_value是变量的初始值。
  • column_name是需要进行分组的列名，对应于GROUP BY语句中的列名。
  • expression是需要合并的列名或表达式。

以上文的订单表（orders）为例，我们可以使用以下语句将订单表中的每个客户的所有产品名称合并为一行：

SET @products := '';
SELECT customer, CONCAT(
        MAX(CASE WHEN @products != product THEN @products := product ELSE '' END),
        ' ',
        MAX(CASE WHEN @products != product THEN @products := product END)
    ) AS products
FROM orders
GROUP BY customer;

输出：

customer products
John Smith iPhone iPad
Mary Brown MacBook iMac

方法四：使用自联结

除了上述方法以外，我们还可以使用自联结（Self-Join）来实现将多行数据合并成一行的效果。

基本语法

SELECT t1.column_name, CONCAT(t2.expression1, t3.expression2, ...) AS merged
FROM table_name t1
JOIN table_name t2 ON t2.column_name = t1.column_name AND t2.id < t1.id
JOIN table_name t3 ON t3.column_name = t1.column_name AND t3.id > t1.id
GROUP BY t1.column_name;
  • t1、t2、t3是自联结的表别名，用于区分不同的自联结表。
  • column_name是需要进行分组的列名，对应于GROUP BY语句中的列名。
  • expression是需要合并的列名或表达式。

以上文的订单表（orders）为例，我们可以使用以下语句将订单表中的每个客户的所有产品名称合并为一行：

SELECT t1.customer, CONCAT(t2.product, ' ', t3.product) AS products
FROM orders t1
JOIN orders t2 ON t2.customer = t1.customer AND t2.order_id < t1.order_id
JOIN orders t3 ON t3.customer = t1.customer AND t3.order_id > t1.order_id
GROUP BY t1.customer;

输出：

customer products
John Smith iPhone iPad
Mary Brown MacBook iMac

总结

本文介绍了MySQL中将多行数据合并成一行的多种方法，包括使用GROUP_CONCAT()函数、子查询和CONCAT()函数、CASE语句和自定义变量、自联结等。不同的方法适用于不同的场景，开发者可以根据实际需求选择合适的方法来实现多行合并成一行的操作。希望本文能够对你在MySQL中处理多行数据合并问题时有所帮助。

参考资料：

这篇文章将讨论如何在MySQL中将多行数据合并成一行。在很多情况下，我们需要通过合并数据来简化查询结果或生成更易读的输出。现在，我们将深入研究几种常用的方法来实现这一目标。

方法一：使用GROUP_CONCAT()函数

GROUP_CONCAT()是MySQL的一个聚合函数，它可以将一个组内的值连接在一起并返回一个字符串。通过使用GROUP BY子句，我们可以将多行数据合并为一行。

基本语法

SELECT column_name, GROUP_CONCAT(expression)
FROM table_name
GROUP BY column_name;
  • column_name是需要分组的列名，对应于GROUP BY语句中的列名。
  • expression是要合并的列名或表达式。

让我们通过一个示例来演示如何使用GROUP_CONCAT()函数。

假设我们有一个名为”students”的表，存储了学生的姓名和他们选择的课程。

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    course VARCHAR(50)
);

INSERT INTO students (id, name, course)
VALUES (1, 'John', 'Math'),
       (2, 'John', 'Science'),
       (3, 'Mary', 'English'),
       (4, 'Mary', 'History');

现在，我们想将每个学生选择的课程合并为一行。

SELECT name, GROUP_CONCAT(course) AS courses
FROM students
GROUP BY name;

输出如下：

name courses
John Math,Science
Mary English,History

通过使用GROUP_CONCAT()函数，我们成功地将学生选择的课程合并为一行。

控制连接字符串和排序

GROUP_CONCAT()函数默认使用逗号作为连接字符串。但是我们可以通过使用SEPARATOR选项来指定连接字符串。

SELECT name, GROUP_CONCAT(course SEPARATOR ';') AS courses
FROM students
GROUP BY name;

输出如下：

name courses
John Math;Science
Mary English;History

此外，我们还可以使用ORDER BY子句对连接的结果进行排序。

SELECT name, GROUP_CONCAT(course ORDER BY course DESC) AS courses
FROM students
GROUP BY name;

输出如下：

name courses
John Science,Math
Mary History,English

方法二：使用子查询和CONCAT()函数

除了使用GROUP_CONCAT()函数，我们还可以使用子查询和CONCAT()函数来实现将多行数据合并成一行的效果。

基本语法

SELECT column_name, CONCAT(expression1, expression2, ...) AS merged
FROM (
    SELECT column_name, expression
    FROM table_name
    GROUP BY column_name
) AS subquery;
  • column_name是需要分组的列名，对应于子查询中的列名。
  • expression是要合并的列名或表达式。

让我们以一个示例说明如何使用子查询和CONCAT()函数。

假设我们有一个名为”orders”的表，存储了客户的订单信息，包括订单号、客户姓名和产品名称。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer VARCHAR(50),
    product VARCHAR(50)
);

INSERT INTO orders (order_id, customer, product)
VALUES (1, 'John Smith', 'iPhone'),
       (2, 'John Smith', 'iPad'),
       (3, 'Mary Brown', 'MacBook'),
       (4, 'Mary Brown', 'iMac');

现在，我们想将每个客户的所有产品名称合并为一行。

SELECT customer, CONCAT(
        (SELECT product FROM orders WHERE customer = o.customer LIMIT 1),
        ' ',
        (SELECT product FROM orders WHERE customer = o.customer LIMIT 1,1)
    ) AS products
FROM (
    SELECT DISTINCT customer
    FROM orders
) AS o;

输出如下：

customer products
John Smith iPhone iPad
Mary Brown MacBook iMac

通过使用子查询和CONCAT()函数，我们成功地将每个客户的所有产品名称合并为一行。

方法三：使用CASE语句和自定义变量

除了上述方法以外，我们还可以使用CASE语句和自定义变量来实现将多行数据合并成一行的效果。

基本语法

SET @var_name := initial_value;

SELECT column_name, CONCAT(
        MAX(CASE WHEN expression1 THEN expression2 ELSE '' END),
        MAX(CASE WHEN expression3 THEN expression4 ELSE '' END),
        ...
    ) AS merged
FROM table_name
GROUP BY column_name;
  • @var_name是自定义变量的名称。
  • initial_value是变量的初始值。
  • column_name是需要分组的列名，对应于GROUP BY子句中的列名。
  • expression是要合并的列名或表达式。

让我们通过一个示例来演示使用CASE语句和自定义变量。

假设我们有一个名为”employees”的表，存储了员工的职位信息，包括员工姓名、职位名称和薪资。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, position, salary)
VALUES (1, 'John', 'Manager', 5000.00),
       (2, 'John', 'Supervisor', 3000.00),
       (3, 'Mary', 'Coordinator', 2000.00),
       (4, 'Mary', 'Assistant', 1500.00);

现在，我们想要将每个员工的职位和薪资合并为一行。

SET @info := '';

SELECT name, CONCAT(
        MAX(CASE WHEN @info != CONCAT(position, ' ', salary) THEN @info := CONCAT(position, ' ', salary) ELSE '' END),
        MAX(CASE WHEN @info != CONCAT(position, ' ', salary) THEN @info := CONCAT(position, ' ', salary) END)
    ) AS info
FROM employees
GROUP BY name;

输出如下：

name info
John Manager 5000.00
Mary Coordinator 2000.00

通过使用CASE语句和自定义变量，我们成功地将每个员工的职位和薪资合并为一行。

方法四：使用自联结

除了上述方法以外，我们还可以使用自联结(Self-Join)来实现将多行数据合并成一行的效果。

基本语法

SELECT t1.column_name, CONCAT(t2.expression1, t3.expression2, ...) AS merged
FROM table_name t1
JOIN table_name t2 ON t2.column_name = t1.column_name AND t2.id < t1.id
JOIN table_name t3 ON t3.column_name = t1.column_name AND t3.id > t1.id
GROUP BY t1.column_name;
  • t1、t2、t3是自联结的表别名，用于区分不同的自联结表。
  • column_name是需要分组的列名，对应于GROUP BY子句中的列名。
  • expression是要合并的列名或表达式。

让我们通过一个示例来演示使用自联结。

假设我们有一个名为”orders”的表，存储了客户的订单信息，包括订单号、客户姓名和产品名称。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer VARCHAR(50),
    product VARCHAR(50)
);

INSERT INTO orders (order_id, customer, product)
VALUES (1, 'John Smith', 'iPhone'),
       (2, 'John Smith', 'iPad'),
       (3, 'Mary Brown', 'MacBook'),
       (4, 'Mary Brown', 'iMac');

现在，我们想将每个客户的所有产品名称合并为一行。

SELECT t1.customer, CONCAT(t2.product, ', ', t3.product) AS products
FROM orders t1
JOIN orders t2 ON t2.customer = t1.customer AND t2.order_id < t1.order_id
JOIN orders t3 ON t3.customer = t1.customer AND t3.order_id > t1.order_id
GROUP BY t1.customer;

输出如下：

customer products
John Smith iPhone, iPad
Mary Brown MacBook, iMac

通过使用自联结，我们成功地将每个客户的所有产品名称合并为一行。

总结：

无论是使用GROUP_CONCAT()函数、子查询和CONCAT()函数、CASE语句和自定义变量，还是使用自联结，我们都可以将多行数据合并成一行。根据具体情况，选择合适的方法来实现这一目标，以满足我们的需求。

Camera课程

Camera API2 教程

API2 教程
Camera 性能教程

Perf 教程
Camera Native Framework 教程

Native FW 教程
Camera QCOM ISP 教程

ISP 教程
Camera QCOM Sensor 教程

Sensor 教程

Python教程

Python 教程

Python 教程
NumPy 教程

NumPy 教程
Django 教程

Django 教程

Java教程

Java 教程

Java 教程

Web教程

JavaScript 教程

JavaScript 教程
jQuery 教程

jQuery 教程
CSS 教程

CSS 教程
PHP 教程

PHP 教程
Laravel 教程

Laravel 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程

数据库教程

SQL 教程

SQL 教程
SQLite 教程

SQLite 教程
MySQL 教程

MySQL 教程
PL/SQL 教程

PL/SQL 教程
MongoDB 教程

MongoDB 教程

图形图像教程

OpenCV 教程

OpenCV 教程

办公软件教程

Excel 教程

Excel 教程

Linux教程

Linux命令全集

Linux命令全集
Linux内核API

Linux内核API
LVGL 教程

LVGL 教程

计算机教程

操作系统 教程

操作系统 教程
计算机网络 教程

计算机网络 教程
C语言 教程

C语言 教程
C++ 教程

C++ 教程
Swift 教程

Swift 教程

大数据教程

Hadoop 教程

Hadoop 教程
Spark 教程

Spark 教程
Scala 教程

Scala 教程

开发工具教程

Git 教程

Git 教程
Jenkins 教程

Jenkins 教程
ChatGPT 教程

ChatGPT 教程
IntelliJ 教程

IntelliJ 教程

MySQL 精选教程