Shulou(Shulou.com)06/01 Report--

In the last article, I updated across tables and saw that the SQL I wrote was like a folly about updating across tables. The year passed quickly, and the two employees in the article, Da Da and Ji er, also had to face the ruthless KPI examination. they did a good job, with an performance of 4 and 5 respectively.

New demand is coming, it is coming quietly! The leader wants to see who is under each performance and requires that their names be concatenated into a string, that is, to get the following result:

What do you do to concatenate a specified column in the result set? The protagonist shines ✨ enters the stage.

GROUP_CONCAT (expr)

In the Mysql official documentation, this function is placed in the aggregate function section. If you want to group and concatenate according to the specified fields, you should use the keyword GROUP BY.

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])

What? This grammar looks too complicated, don't worry, the following examples will slowly illustrate the verification one by one.

Use case

First complete the requirements at the beginning of the article:

SELECT performance, GROUP_CONCAT (employee_name) AS employeesFROM employeesGROUP BY performance

Zou is the result:

At this point, the requirements given by the leader will complete the 😜.

Sir, please stay. Your order is not finished yet.

We are an international team, and our hometown is everywhere.

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

SELECT GROUP_CONCAT (DISTINCT home_town) FROM employees

Let's look at the results:

The leader's concern is everywhere.

The copywriting needs to be changed, and the leaders' care is spread all over the four seas and lakes, so the ORDER BY keyword will come in handy.

SELECT GROUP_CONCAT (DISTINCT home_town ORDER BY home_town DESC) AS 'leadership care area' FROM employees;-- is not as variable as I am, it's still Chinese, I think you're crazy.

Here you can see that the default separator of the GROUP_CONCAT function concatenation string is a comma, the leader is unhappy, the feeling of comma, you have to use ❕ to show the intensity of care, the SEPARATOR keyword will come in handy.

The default delimiter between values for grouping is a comma (,). To specify the delimiter explicitly, you need to use the SEPARATOR keyword, followed by the separator you want to set. To completely eliminate the delimiter, write''just SELECT GROUP_CONCAT (DISTINCT home_town ORDER BY home_town DESC SEPARATOR'!') after the SEPARATOR keyword. AS 'leadership Care area' FROM employees

SELECT GROUP_CONCAT (DISTINCT home_town SEPARATOR'') AS 'lead caring area' FROM employees

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

The leader's ability to care is also limited. The default maximum length of the concatenated string is 1024 characters. You can check the current limit by using the following statement:

Show variables like 'group_concat_max_len'

The ability of leadership is erratic, so we can set this value flexibly.

SET [GLOBAL | SESSION] group_concat_max_len = val

SESSION: takes effect in the current conversation GLOBAL: works globally

This statement works until MySQL is restarted after it is executed. Once MySQL is restarted, the default value will be restored.

Sometimes GROUP_CONCAT () has to play a little bit of power with CONCAT_WS (), to give a simple example

Separate the first and last names of consumers with commas, and then use them; separate SELECT GROUP_CONCAT (CONCAT_WS (',', contactLastName, contactFirstName) SEPARATOR';') FROM customers

Here is the usage of the CONCAT_WS () function. It's very simple. Please check it yourself.

Pay attention to ⚠️

The GROUP_CONCAT () function returns a single string instead of 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, nameFROM table_nameWHERE id IN GROUP_CONCAT (id)

Summary

In many cases, we can use the GROUP_CONCAT () function to produce useful results, and we can also combine other functions to play a more powerful role. If you have learned the single trick, you have to learn to recruit combo in a row.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.