MySQL concat and group_concat usage, concatgroup_concat

MySQL concat and group_concat usage, concatgroup_concat

The examples used in this article are all executed in the following database table tt2:

 

1. concat () function

1. function: concatenate multiple strings into one string.

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

The returned result is a string generated by the connection parameter. If any of the input values is null, the return value is null.

3. Example:

Example 1: select concat (id, name, score) as info from tt2;

There is a null behavior in the middle because the score value of one row in the tt2 table is null.

Example 2: In the result of Example 1, the combination of the three fields id, name, and score has no separator. we can add a comma as the separator:

It seems quite pleasing to the eye ~~

However, it is a lot of trouble to input SQL statements. Three fields need to be entered with two commas. If 10 fields need to be entered with nine commas... Is there any easy way to do this? -- You can specify the concat_ws () separator between parameters !!!

 

2. concat_ws () function

1. function: Like concat (), multiple strings are connected to one character string, but separators can be specified at a time ~ (Concat_ws is concat with separator)

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

Description: The first parameter specifies the delimiter. Note that the delimiter cannot be null. If it is null, the return result is null.

3. Example:

Example 3: Use concat_ws () to specify the separator as a comma to achieve the same effect as Example 2:

Example 4: Specify the delimiter as null and all results are converted to null:

 

3. group_concat () function

In a query statement with group by, the field specified by select is either included behind the group by statement and used as the basis for grouping or included in aggregate functions. (For more information about group by, see <G id = "1"> SQL Group By </G> ).

Example 5:

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

Of course, we can query it like this:

Example 6:

However, the same name appears multiple times, which looks very inintuitive. Is there a more intuitive way to make every name appear only once and display the IDs of all persons with the same name? -- Use group_concat ()

1. function: Concatenates the values in the same group generated by group by and returns a string.

2. Syntax: group_concat ([distinct] field to be connected [order by sorting field asc/desc] [separator 'delimiter'])

Note: You can use distinct to exclude duplicate values. If you want to sort the values in the result, you can use the order by clause. separator is a string value and the default value is a comma.

3. Example:

Example 7: Use group_concat () and group by to display the ID number of the person with the same name:

Example 8: sort the ID number from large to small and use '_' as the separator:

Example 9: The above query shows all IDs in each group grouped by name. Next, we will query the IDs and scores of all groups grouped by name: