Detailed explanation of the method of converting MySQL columns to rows and merging fields (pictures and texts)-Mysql Tutorial-php.cn

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

黄舟

Release： 2017-03-25 13:26:22

Original

1361 people have browsed it

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:

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

##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=&#39;语文&#39; THEN
   score
  END
 ) AS 语文,
 MAX(
  CASE 
  WHEN course=&#39;数学&#39; THEN
   score
  END
 ) AS 数学, 
 MAX(
  CASE 
  WHEN course=&#39;英语&#39; THEN
   score
  END
 ) AS 英语
FROM
 student
GROUP BY `name`
;

Copy after login

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

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

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

Copy after login

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

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`;

Copy after login

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

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

2 .Test:

SELECT
 GROUP_CONCAT(`name`)
FROM
 student
;

Copy after login

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 Detailed explanation of the method of converting MySQL columns to rows and merging fields (pictures and texts)

3. Test

ConstantConfiguration impact on group_concat():

SET @@GROUP_CONCAT_MAX_LEN=4

Copy after login

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

SET [SESSION | GLOBAL] group_concat_max_len = val;

Copy after login

two What's the difference?

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

Copy after login

4. Use the statement

SELECT
GROUP_CONCAT(`name`)
FROM
student;

Copy after login

and the result will be:

group_concat(&#39;name&#39;)

Copy after login

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

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
;

Copy after login

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`;

Copy after login

Then the displayed result is similar to this:

group_concat(course,":",score)

Copy after login

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!