In the last article, I saw that I wrote SQL like a silly thing about cross-table updates. The year passed quickly, and the two later employees, Feng Da and Feng Er, also had to face the ruthless KPI assessment. They did very well in their work, and their performance was 4 and 5 respectively

New demand is coming, quietly coming!! The leader wants to see who is present under each performance, and asks to concatenate the names of these people into a string with commas, which yields the following result:

How do I concatenate a specified column in the result set? The leading role ✨ debut

GROUP_CONCAT(expr)

In the Mysql documentation, this function is listed in the aggregate functions section, and is used with the GROUP BY keyword if you want to GROUP BY the specified field

define

This function returns a string result that is a non-null value concatenated by grouping. If there is no non-NULL value, NULL is returned. The complete syntax is as follows:

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

What? This syntax seems rather complicated, but don’t worry, we will use examples to verify it

Use case

Complete the requirements at the beginning of the article first:

SELECT performance.GROUP_CONCAT(employee_name) AS employees
FROM employees
GROUP BY performance;
Copy the code

Zou is the result:

Here, the leadership to come over the demand is completed 😜

Guest officer please stay, you ordered food has not finished it……

We are an international team, our hometown is all over the world

The leader wants to care about the employees, to check the hometown of all the employees of the company. Employees may come from the same place, so the DISTINCT keyword comes in handy for duplicating the result set

SELECT GROUP_CONCAT(DISTINCT home_town)
FROM employees;
Copy the code

The results:

The leadership of the care all over the world……

The leader’s care is everywhere, so the keyword “ORDER BY” will come in handy

SELECT GROUP_CONCAT(DISTINCT home_town ORDER BY home_town DESC) AS 'Leadership Caring District'
FROM employees;

-- Not so variable I ha, or Chinese, I see you are crazy
Copy the code

Here you see the GROUP_CONCAT function concatenates the string with a comma as the default SEPARATOR. If the leader is unhappy and the feeling is comma, use ❕ to express the strong concern. SEPARATOR keyword comes in handy

The default separator between grouped concatenated values is a comma (,). To specify the SEPARATOR explicitly, use the SEPARATOR keyword, followed by the SEPARATOR you want. To eliminate SEPARATOR completely, just write “” after the SEPARATOR keyword

SELECT GROUP_CONCAT(DISTINCT home_town ORDER BY home_town DESC SEPARATOR '! ') AS 'Leadership Caring District'
FROM employees;
Copy the code

SELECT GROUP_CONCAT(DISTINCT home_town SEPARATOR ' ') AS 'Leadership Caring District'
FROM employees;
Copy the code

This care in place, you taste, you fine taste!!

The default maximum length of a concatenated string is 1024 characters. You can check the current limit by using the following statement:

show variables like 'group_concat_max_len';
Copy the code

Leadership is fluid, so we can be flexible about setting this value

SET [GLOBAL | SESSION] group_concat_max_len = val;
Copy the code
  • SESSION: Takes effect in the current SESSION
  • GLOBAL: this takes effect globally

This statement takes effect until MySQL restarts. Once MySQL restarts, the default value will be restored

Sometimes GROUP_CONCAT() is paired with CONCAT_WS() for a bit of power, for a simple example

Separate the consumer’s first and last name with a comma before using separation

SELECT
    GROUP_CONCAT(
       CONCAT_WS(', ', contactLastName, contactFirstName)
       SEPARATOR '; ')
FROM
    customers;
Copy the code

Here is the CONCAT_WS() function usage, very simple, please check it out……

Note ⚠ ️

The GROUP_CONCAT () function returns a single string, not a list of values. This means that we cannot use the result of the GROUP_CONCAT () function IN the IN operator, for example, IN a subquery like this:

SELECT
    id.name
FROM
    table_name
WHERE
    id IN GROUP_CONCAT(id);
Copy the code

conclusion

In many cases, we can use the GROUP_CONCAT () function to produce useful results, or combine it with other functions for more power. If you can do one, you have to do one

  • If you’re just starting out like me, please click “Watching.”
  • If you already know this, please leave a comment and hiss.

Soul asking

  1. What are the limitations of combining aggregate functions with group By?

  2. What other aggregate function operations can you immediately think of besides sum?

Personal blog: https://dayarch.top

Add my wechat friends, enter the group entertainment learning exchange, note “enter the group”

Welcome to continue to pay attention to the public account: “One soldier of The Japanese Arch”

  • Cutting-edge Java technology dry goods sharing
  • Efficient tool summary | back “tool”
  • Interview question analysis and solution
  • Technical data to receive reply | “data”

To read detective novel thinking easy fun learning Java technology stack related knowledge, in line with the simplification of complex problems, abstract problems concrete and graphical principles of gradual decomposition of technical problems, technology continues to update, please continue to pay attention to……