MySQL column to row conversion, method of merging fields (must read)

Data Sheet:

Column to row: using max(case when then)

max---aggregate function takes the maximum value

(case course when 'Chinese' then score else 0 end) ---Judgement

as language---alias as column name

SELECT
 `name`,
 MAX(
  CASE 
  WHEN course = 'Chinese' THEN
   score
  END
 ) AS Language,
 MAX(
  CASE 
  WHEN course = 'Mathematics' THEN
   score
  END
 ) AS Mathematics, 
 MAX(
  CASE 
  WHEN course = 'English' THEN
   score
  END
 ) AS English FROM
 student
GROUP BY `name`
; 

Merge field display: Use group_cancat(course,":","score")

SELECT
 `name`,
 GROUP_CONCAT(course, ":", score) AS score FROM
 student
GROUP BY
 `name`; 

group_concat(), the manual states: This function returns a string result with non-NULL values ​​from a group connection.
It is relatively abstract and difficult to understand.

To put it simply, group_concat() will calculate which rows belong to the same group and display the columns that belong to the same group. Which columns to return is determined by the function

The number of parameters (that is, the field name) determines. There must be a standard for grouping, that is, grouping according to the column specified by group by.

My guess is that the group_concat function should execute the group by statement internally.

1. Test statement:

SELECT
 GROUP_CONCAT(`name`)
FROM
 student
GROUP BY
 `name`;

The result is to find which values ​​in name are the same. If they are equal, list them all, separated by commas, as follows:

group_concat('name')

2. Test:

SELECT
 GROUP_CONCAT(`name`)
FROM
 student
;

result:

group_concat('name')

Can it be proved that group_concat can only be effective when used with a group by statement? The following is a practical test

3. Test the effect of constants on the configuration of group_concat():

SET @@GROUP_CONCAT_MAX_LEN=4

The manual states that the syntax for the setting is:

SET [SESSION | GLOBAL] group_concat_max_len = val;

What is the difference between the two?

SET @@global.GROUP_CONCAT_MAX_LEN=4;
global can be omitted, then it becomes: SET @@GROUP_CONCAT_MAX_LEN=4;

4. Use statements

SELECT
GROUP_CONCAT(`name`)
FROM
student;


The result is:

group_concat('name')

Conclusion: The group_concat() function needs to be used together with the group by statement to achieve the desired effect.

The reason can be understood as follows: group_concat() obtains all members belonging to group x (the column parameters in the function specify which fields need to be displayed). Where does group x come from?

If no group by is specified, then it is not known which group group_concat() will use to display the members. So, when there is no group by clause above, it shows Liu Bei, Guan Yu, Zhang Fei, Liu Bei, Guan Yu, Zhang Fei, Liu Bei, Guan Yu, Zhang Fei.

When is this function actually needed?

Suppose the query result is as follows: the group name is displayed on the left, and all member information under the group is displayed on the right. Using this function can save you a lot of trouble.

Also, if I use:

SELECT
 `name`,
 GROUP_CONCAT(course, ":", score) AS score FROM
 student
;

It doesn't make much sense.

Specifying a single column is the best approach for group_concat(). If multiple columns are specified.

SELECT
 `name`,
 GROUP_CONCAT(course, ":", score) AS score FROM
 student
GROUP BY
 `name`;

Then the displayed result is similar to this:

group_concat(course,":",score)

The above article on how to convert columns to rows and merge fields in MySQL (must read) is all I want to share with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.