Mysql column-to-row, merge fields (mandatory), mysql Fields

Mysql column-to-row, merge fields (mandatory), mysql Fields

Data Table:

Column-to-row: Use max (case when then)

Max --- maximum value for Aggregate functions

(Case course when 'chine' then score else 0 end) --- judgment

As language --- alias as column name

SELECT 'name', MAX (case when course = 'China' THEN score END) AS language, MAX (case when course = 'mate' THEN score END) AS mathematics, MAX (case when course = 'English 'THEN score END) AS English FROM studentGROUP BY 'name ';

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

SELECT 'name', GROUP_CONCAT (course, ":", score) AS score FROM studentGROUP BY 'name ';

Group_concat (), Manual Description: This function returns a string with a non-NULL value from a group of connections.
It is abstract and hard to understand.

Generally, group_concat () calculates which rows belong to the same group and displays the columns belonging to the same group. The columns to be returned.

The number parameter (that is, the field name) is determined. The group must have a standard, that is, grouping based on the columns specified by group.

The group_concat function should have executed the group by statement internally, which is my guess.

1. Test statement:

SELECT GROUP_CONCAT(`name`)FROM studentGROUP BY `name`;

The result shows which values are the same in the search name. If they are equal, all values are listed and separated by commas (,), as shown below:

Group_concat ('name ')

2. test:

SELECT GROUP_CONCAT(`name`)FROM student;

Result:

Group_concat ('name ')

Does the above prove that group_concat can produce results only when used together with the group by statement? The following is a practical test

3. Test the configuration impact of Constants on group_concat:

SET @ GROUP_CONCAT_MAX_LEN = 4

The syntax mentioned in the manual is as follows:

SET [SESSION | GLOBAL] group_concat_max_len = val;

What are the differences between the two types?

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

4. Use statements

SELECT
GROUP_CONCAT ('name ')
FROM
Student;


The result is as follows:

Group_concat ('name ')

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

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

If you do not specify group by, you do not know which group group_concat () displays the members. Therefore, 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, and Zhang Fei.

When does this function actually need to be used?

If the query result is as follows: the group name is displayed on the left and all members in the group are displayed on the right. With this function, you can save a lot of things.

In addition, if I use:

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

Not significant.

Group_concat () specifies a column, which is the best case. If multiple columns are specified.

SELECT 'name', GROUP_CONCAT (course, ":", score) AS score FROM studentGROUP BY 'name ';

The displayed result is similar to the following:

Group_concat (course, ":", score)

In the above mysql column-to-row, the method of merging fields (mandatory) is all the content shared by the editor. I hope you can provide a reference and support for the customer's house.