This article mainly introducesmysqlrow to column and column to row examples of detailed explanation of relevant information, friends in need can refer to
mysql row to column, column to row
The statement is not difficult, so I won’t explain it too much. When reading the statement, analyze it sentence by sentence from the inside out
Row to column
that are currently expected to have the rows converted into columns as a result of the query and now wish to convert the rows into columns. Query statement:
The reason why MAX is used here is to set the points with no data to 0 to prevent NULL
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;Result display:
Column to row
There is a table as shown in the figure, and now I hope that the query results will be listed in rowsThe table creation statement is as follows:
insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE) values ("张三", "数学", 34), ("张三", "语文", 58), ("张三", "英语", 58), ("李四", "数学", 45), ("李四", "语文", 87), ("李四", "英语", 45), ("王五", "数学", 76), ("王五", "语文", 34), ("王五", "英语", 89);
SELECT user_name , MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学, MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文, MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语 FROM test_tb_grade GROUP BY USER_NAME;
Query statement:
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;
Results display:
The above is the detailed content of Sample code sharing to implement mysql row to column and column to row conversion. For more information, please follow other related articles on the PHP Chinese website!