Detailed description of mysql row-to-column and column-to-row instances, mysql instances

Detailed description of mysql row-to-column and column-to-row instances, mysql instances

Mysql row-to-column and column-to-row

It is not difficult to explain the statement. When you look at the statement, explain the sentence from the inside out.

Row-to-Column

In some tables, you want to convert the query results into columns.

The table creation statement is as follows:

CREATE TABLE `TEST_TB_GRADE` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `USER_NAME` varchar(20) DEFAULT NULL, `COURSE` varchar(20) DEFAULT NULL, `SCORE` float DEFAULT '0', PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Insert into TEST_TB_GRADE (USER_NAME, COURSE, SCORE) values ("Zhang San", "Mathematics", 34), ("Zhang San", "", 58), ("Zhang San ", "English", 58), ("Li Si", "Mathematics", 45), ("Li Si", "Chinese", 87), ("Li Si", "English ", 45), ("Wang Wu", "Mathematics", 76), ("Wang Wu", "", 34), ("Wang Wu", "English", 89 );

Query statement:

MAX is used to set the point without data to 0 to prevent NULL.

SELECT user_name, MAX (CASE course WHEN 'mate' THEN score ELSE 0 END), MAX (CASE course WHEN 'chine' THEN score ELSE 0 END, MAX (CASE course WHEN 'then score ELSE 0 END) English FROM test_tb_gradeGROUP BY USER_NAME;

Result Display:

Column-to-row

In some tables, you want the query results to be listed in rows.

The table creation statement is as follows:

CREATE TABLE `TEST_TB_GRADE2` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `USER_NAME` varchar(20) DEFAULT NULL, `CN_SCORE` float DEFAULT NULL, `MATH_SCORE` float DEFAULT NULL, `EN_SCORE` float DEFAULT '0', PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Insert into TEST_TB_GRADE2 (USER_NAME, CN_SCORE, MATH_SCORE, EN_SCORE) values ("Zhang San", 34, 58, 58), ("Li Si", 45, 87, 45 ), ("Wang Wu", 76, 34, 89 );

Query statement:

Select user_name, 'China' COURSE, CN_SCORE as SCORE from test_tb_grade2union select user_name, 'mate' COURSE, MATH_SCORE as SCORE from test_tb_grade2union select user_name, 'English 'COURSE, EN_SCORE as SCORE from test_tb_grade2order by user_name, COURSE;

Result Display:

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!