好的,我添加一个在所有示例中使用的表结构和测试数据,对于不需要表的示例,简单移除相关部分即可。

How the GROUP_CONCAT() function works in MariaDB?

The MariaDB GROUP_CONCAT() function is used to concatenate values from rows into a single string, grouped by a specified column or expression. It is particularly useful when you need to combine multiple rows into a single comma-separated string, enabling you to represent a set of values in a single row.

Syntax

The syntax for the MariaDB GROUP_CONCAT() function is as follows:

GROUP_CONCAT([DISTINCT] expr [, expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name ...]] [SEPARATOR str_val])

• DISTINCT: An optional keyword that removes duplicate values from the result.
• expr: The expression(s) to be concatenated. Multiple expressions can be specified, separated by commas.
• ORDER BY: An optional clause that specifies the order in which the values should be concatenated. You can order by an unsigned integer, column name, or expression, followed by ASC (ascending) or DESC (descending) order. Multiple columns can be specified for sorting.
• SEPARATOR: An optional string value that separates the concatenated values. If omitted, the default separator is a comma (,).

The GROUP_CONCAT() function returns a string result.

Examples

Example 1: Basic Usage

This example demonstrates the basic usage of the GROUP_CONCAT() function.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie');

SELECT GROUP_CONCAT(name) FROM employees;

The output will be:

+-----------------------------+
| GROUP_CONCAT(name)          |
+-----------------------------+
| John,Jane,Alice,Bob,Charlie |
+-----------------------------+

In this example, the GROUP_CONCAT() function concatenates all the names from the employees table into a single string, separated by commas.

Example 2: Using DISTINCT

This example shows how to use the DISTINCT keyword to remove duplicate values.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie'),
    (6, 'John');

SELECT GROUP_CONCAT(DISTINCT name) FROM employees;

The output will be:

+-----------------------------+
| GROUP_CONCAT(DISTINCT name) |
+-----------------------------+
| Alice,Bob,Charlie,Jane,John |
+-----------------------------+

If there are duplicate names in the employees table, the DISTINCT keyword will ensure that each name appears only once in the concatenated string.

Example 3: Ordering the Concatenated Values

This example demonstrates how to order the concatenated values using the ORDER BY clause.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie');

SELECT GROUP_CONCAT(name ORDER BY name DESC) FROM employees;

The output will be:

+---------------------------------------+
| GROUP_CONCAT(name ORDER BY name DESC) |
+---------------------------------------+
| John,Jane,Charlie,Bob,Alice           |
+---------------------------------------+

In this example, the names are concatenated in descending alphabetical order.

Example 4: Using a Custom Separator

This example shows how to use a custom separator instead of the default comma.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie');

SELECT GROUP_CONCAT(name SEPARATOR ' | ') FROM employees;

The output will be:

+-------------------------------------+
| GROUP_CONCAT(name SEPARATOR ' | ')  |
+-------------------------------------+
| John | Jane | Alice | Bob | Charlie |
+-------------------------------------+

In this example, the names are concatenated and separated by the | character.

Example 5: Concatenating Multiple Expressions

This example demonstrates how to concatenate multiple expressions using the GROUP_CONCAT() function.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

INSERT INTO employees (id, name, department)
VALUES
    (1, 'John', 'Sales'),
    (2, 'Jane', 'Marketing'),
    (3, 'Alice', 'IT'),
    (4, 'Bob', 'Sales'),
    (5, 'Charlie', 'IT');

SELECT
    GROUP_CONCAT(name, ' (', department, ')') AS employee_info
FROM
    employees;

The output will be:

+-------------------------------------------------------------------+
| employee_info                                                     |
+-------------------------------------------------------------------+
| John (Sales),Jane (Marketing),Alice (IT),Bob (Sales),Charlie (IT) |
+-------------------------------------------------------------------+

In this example, the GROUP_CONCAT() function concatenates the name and department columns, separated by a space and parentheses.

Related Functions

The following are a few functions related to the MariaDB GROUP_CONCAT() function:

• MariaDB CONCAT() function is used to concatenate two or more strings into a single string.
• MariaDB CONCAT_WS() function is used to concatenate strings with a separator.
• MariaDB STRING_AGG() function is similar to GROUP_CONCAT(), but it allows you to specify an order by clause for each row.
• MariaDB GROUP_CONCAT_DISTINCT() function is a non-standard extension that combines the functionality of GROUP_CONCAT() and DISTINCT.

Conclusion

The MariaDB GROUP_CONCAT() function is a powerful tool for combining multiple values from different rows into a single string, making it easier to represent and manipulate data in a more compact format. By understanding its syntax and usage, you can leverage this function to solve a wide range of data manipulation challenges efficiently.