MySQL: A brief introduction to the Concat and Group_concat use methods

The function of the concat () function: concatenate multiple strings into a single string. Syntax: concat (str1, str2,...) Returns the string resulting from the connection parameter, or null if any one of the arguments is null.

Group_concat the multiline value into a row after grouping, separating the values of the rows by commas

Description

The examples used in this article are executed under the following database table TT2:

One, concat () function

1. Function: Concatenate multiple strings into a single string.

2. Syntax: Concat (str1, str2,...)

Returns the string resulting from the connection parameter, or null if any one of the arguments is null.

3. For example:

Example 1:select concat (ID, name, score) as info from TT2;

There is a behavior in the middle that is null because the score value of a row in the TT2 table is null.

Example 2: in Example 1 of the results of three fields id,name,score the combination of no separators, we can add a comma as a delimiter:

This seems to be pleasing to the eye a lot ~ ~

But the input SQL statement is a lot of trouble, three fields need to enter two comma, if 10 fields, to enter nine times comma ... The trouble is dead, is there any easy way? --so you can specify the delimiter between the parameters of the CONCAT_WS () Come!!!

Second, Concat_ws () function

1. Function: As with concat (), concatenate multiple strings into a single string, but you can specify the delimiter at once (Concat_ws is concat with separator)

2. Syntax: CONCAT_WS (separator, str1, str2, ...)

Description: The first parameter specifies a delimiter. It is important to note that the delimiter cannot be null, and if NULL, the returned result is null.

3. For example:

Example 3: We use CONCAT_WS () to specify the delimiter as a comma to achieve the same effect as in Example 2:

Example 4: The delimiter is specified as null and the result becomes null:

Three, Group_concat () function

Preface: In a query statement with GROUP BY, the field specified by select is either included after the group by statement, as a basis for grouping, or contained in an aggregate function. (For group by Knowledge, Poke: An analysis of the use of group by in SQL).

Example 5:

This example queries the smallest ID of the same person with name. What if we want to query all the IDs of people with the same name?

Of course we can query:

Example 6:

But the same name appears many times, and it looks very intuitive. Is there a more intuitive way to make each name appear only once, and to show the ID of all the people with the same name? --Using Group_concat ()

1. Function: Concatenate the values in the same group that the group by produces, returning a string result.

2. Syntax: Group_concat ([distinct] field to connect [Order by sort field ASC/DESC] [separator ' delimiter '])

Description: You can exclude duplicate values by using DISTINCT, and if you want to sort the values in the results, you can use the ORDER BY clause; separator is a string value that defaults to a comma.

3. For example:

Example 7: ID number of the person using Group_concat () and group by to display the same name:

Example 8: Sort the above ID number from large to small and use ' _ ' as the delimiter:

Example 9: All the IDs in each group grouped by name are shown in the query above. Next we want to query the IDs and score for all groups grouped by name:

Related articles:

Concat and Concat_ws () differences and Group_concat (), repeat () string functions

The use of group by combined Group_concat in MySQL

Related videos:

Apache Introduction Installation and MySQL Introduction installation use