MySQL 将多行数据合并成单个结果行

当我们使用MySQL数据库时，有时需要将多行数据合并成一行的结果。这种情况在数据分析中很常见。在这篇文章中，我们将介绍一些方法可以将多行数据合并成单个结果行。

阅读更多：MySQL 教程

基础查询

首先，我们来看一下基础查询语句。假设我们有以下students表：

id	name	course	score
1	Alice	Math	80
1	Alice	Music	90
2	Bob	Math	70
2	Bob	Music	95

要获取每个学生的成绩，我们可以使用以下查询：

SELECT id, name, course, score
FROM students;

这会返回每一行数据，其中每个学生有两行记录：一行为Math成绩，一行为Music成绩。但是，如果我们想要每个学生的成绩合并为一行，则需要进行合并查询。

GROUP_CONCAT函数

GROUP_CONCAT函数可以将多个行合并为单个字符串，使用逗号分隔每个元素。让我们尝试使用GROUP_CONCAT函数来获取每个学生的成绩：

SELECT id, name, GROUP_CONCAT(course, ':', score SEPARATOR ', ') AS grades
FROM students
GROUP BY id, name;

这个查询使用了GROUP BY语句，它告诉MySQL将相同id和name的行分组在一起。然后，我们使用GROUP_CONCAT函数将每个分组的course和score列合并为单个字符串，并在每个元素之间使用逗号和冒号分隔符。这个查询将返回以下结果：

id	name	grades
1	Alice	Math:80, Music:90
2	Bob	Math:70, Music:95

结果已经将每个学生的成绩合并为一行，并使用了GROUP_CONCAT函数内置的逗号分隔符，使结果更易于阅读。我们还可以使用ORDER BY子句对组内元素排序：

SELECT id, name, GROUP_CONCAT(course, ':', score ORDER BY course DESC SEPARATOR ', ') AS grades
FROM students
GROUP BY id, name;

在这个例子中，我们对course列进行降序排序，以便输出结果中的课程列表是按字母顺序反转的。这会返回以下结果：

id	name	grades
1	Alice	Music:90, Math:80
2	Bob	Music:95, Math:70

子查询

另一种查询多行结果的方法是使用子查询。假设我们有以下orders表：

order_id	customer	product	quantity
1	John	Apple	5
2	John	Banana	3
3	Alice	Apple	2
4	Bob	Pear	1

我们想为每个客户获取他们购买的商品名称的列表。我们可以使用以下查询：

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

在这个查询中，我们使用了一个子查询，该子查询使用GROUP_CONCAT函数将每个客户的订单商品名称合并为单个字符串。在子查询中，我们使用了customers别名来引用外部查询中的结果。外部查询将每个客户分组在一起，并将结果与子查询合并。

该查询将返回以下结果：

customer	products
John	Apple, Banana
Alice	Apple
Bob	Pear ## GROUP BY的“WITH ROLLUP”选项

我们还可以使用GROUP BY语句的“WITH ROLLUP”选项为最后一行添加汇总信息。例如，如果我们使用GROUP_CONCAT函数按客户姓名获取每个客户的订单商品，我们可以使用以下查询：

SELECT IFNULL(customer, "Total") AS customer, 
       GROUP_CONCAT(product SEPARATOR ', ') AS products
FROM orders
GROUP BY customer WITH ROLLUP;

这个查询将返回以下结果：

customer	products
Alice	Apple
Bob	Pear
John	Apple, Banana
Total	Apple, Banana, Pear

通过使用WITH ROLLUP选项，MySQL将在最后添加一行，其中第一列将全为空值，第二列将包含为每个客户组合并的商品列表。这行告诉我们所有客户的汇总信息。

LEFT JOIN和GROUP BY

如果我们想获取每个顾客的订单数量和商品列表，即使他们没有下过订单，我们可以使用左连接（LEFT JOIN）和GROUP BY来获取所有顾客的信息。下面是一个查询：

SELECT customers.customer, 
       COUNT(orders.order_id) AS order_count, 
       GROUP_CONCAT(orders.product SEPARATOR ', ') AS products
FROM customers 
LEFT JOIN orders ON customers.customer = orders.customer
GROUP BY customers.customer WITH ROLLUP;

这个查询使用了LEFT JOIN，将customers表与orders表连接起来，以便我们获取所有客户的数据，即使他们没有下过订单。接下来，我们使用GROUP BY语句按客户姓名进行分组，并使用COUNT函数计算每个客户的订单数量。最后，我们使用GROUP_CONCAT函数获取每个客户的所有商品名称。该查询将返回以下结果：