Detailed explanation of the method of converting MySQL columns to rows and merging fields (pictures and texts)

The following editor will bring you an articlemysql How to convert columns to rows and merge fields (must read). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

Data table:

##Column switching: use max( case when then)

max---

AggregationFunction Take the maximum value

(case course when '中文' then score else 0 end) ---Judgment

as Chinese---alias as column name

SELECT
 `name`,
 MAX(
  CASE 
  WHEN course='语文' THEN
   score
  END
 ) AS 语文,
 MAX(
  CASE 
  WHEN course='数学' THEN
   score
  END
 ) AS 数学, 
 MAX(
  CASE 
  WHEN course='英语' THEN
   score
  END
 ) AS 英语
FROM
 student
GROUP BY `name`
;

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

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

group_concat(), the manual states: This function returns a non-

with a connection from a group The string result of a NULL value. is relatively abstract and difficult to understand.

To understand it in a simple way, it is actually like this: group_concat() will calculate which rows belong to the same group and display the columns belonging to the same group. Which columns to return are determined by the function parameters (that is, the field names). There must be a standard for grouping, which is to group based on the column specified by group by.

The group_concat function should execute the group by statement internally. This is my guess.

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, listed as follows:

group_concat('name')

2 .Test:

SELECT
 GROUP_CONCAT(`name`)
FROM
 student
;

Result:

group_concat('name')

Can the above prove that group_concat can only have an effect when used together with the group by statement? The following is an actual test

3. Test

ConstantConfiguration impact on group_concat():

SET @@GROUP_CONCAT_MAX_LEN=4

The manual mentions that the syntax for setting is as follows:

SET [SESSION | GLOBAL] group_concat_max_len = val;

two What's the difference?

SET @@global.GROUP_CONCAT_MAX_LEN=4;
global可以省略，那么就变成了：SET @@GROUP_CONCAT_MAX_LEN=4;

4. Use the statement

SELECT
GROUP_CONCAT(`name`)
FROM
student;

and the result will be:

group_concat('name')

Conclusion: The

group_concat() function needs to be used together with the group by statement to get the required effect. The reason can be understood like this: group_concat() gets all members belonging to group x (the column parameters in the function specify which fields need to be displayed). Where did group x come from?

If there is no group by specified, then there is no way to know which group group_concat() will display the members according to. Therefore, when there is no group by clause like the above, Liu Bei, Guan Yu, Zhang Fei, Liu Bei, Guan Yu, Zhang Fei, Liu Bei, Guan Yu, Zhang Fei are displayed.

When is this function actually needed?

If you needquery

, the 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, you can save a lot of things.

In addition, if I use it like this:

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

It doesn’t make much sense.

group_concat() Specifying a column is the best case. If multiple columns are specified.

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

Then the displayed result is similar to this:

group_concat(course,":",score)

The above is the detailed content of Detailed explanation of the method of converting MySQL columns to rows and merging fields (pictures and texts). For more information, please follow other related articles on the PHP Chinese website!