Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this article "how to convert the rows of mysql to columns", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to convert the rows of mysql to columns" article.

The method of mysql row to column: 1, using "SUM (CASE table name WHEN field name THEN score ELSE 0 END) as field name" operation conversion; 2, using "SUM (table name = field name, score,0) as field name" operation conversion.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to convert rows of mysql to columns

Row to column

That is, the different contents of multiple rows under the same column are used as multiple fields to output the corresponding content.

Construction table sentence

DROP TABLE IF EXISTS tb_score;CREATE TABLE tb_score (id INT (11) NOT NULL auto_increment, userid VARCHAR (20) NOT NULL COMMENT 'user id', subject VARCHAR (20) COMMENT' subject', score DOUBLE COMMENT 'grade', PRIMARY KEY (id)) ENGINE = INNODB DEFAULT CHARSET = utf8

Insert data

INSERT INTO tb_score (userid,subject,score) VALUES (001Zongjue 'Chinese', 90); INSERT INTO tb_score (userid,subject,score) VALUES ('001Zhi' Math', 92); INSERT INTO tb_score (userid,subject,score) VALUES ('001Jing' English', 80); INSERT INTO tb_score (userid,subject,score) VALUES ('002' Chinese', 88); INSERT INTO tb_score (userid,subject,score) VALUES ('002Math', 90) INSERT INTO tb_score (userid,subject,score) VALUES (002 English, 75.5); INSERT INTO tb_score (userid,subject,score) VALUES (003, Chinese, 70); INSERT INTO tb_score (userid,subject,score) VALUES (003, mathematics, 85); INSERT INTO tb_score (userid,subject,score) VALUES (003, English, 90); INSERT INTO tb_score (userid,subject,score) VALUES (003, politics, 82)

Query the contents of the data table (that is, the results before conversion)

SELECT * FROM tb_score

Let's first take a look at the result of the conversion:

As you can see, the row transfer column here selects the multi-row contents of the original subject field as different columns in the result set, and displays the corresponding score in groups according to userid.

1. Use case...when....then to transfer rows and columns

SELECT userid,SUM (CASE `Secrett` WHEN 'language' THEN score ELSE 0 END) as' Chinese', SUM (CASE `Secrett` WHEN 'THEN score ELSE 0 END) as' Mathematics', SUM (CASE `Secrett` WHEN 'English' THEN score ELSE 0 END) as' English', SUM (CASE `Secrett`WHEN 'Politics' THEN score ELSE 0 END) as' Politics' FROM tb_score GROUP BY userid

2. Use IF () to transfer rows to columns:

SELECT userid,SUM (IF (`Secrett` = 'Chinese', score,0)) as' Chinese', SUM (IF (`Secrett` = 'Mathematics', score,0)) as' Mathematics', SUM (IF (`English'= 'English', score,0)) as' English', SUM (IF (`polit` = 'Politics', score,0)) as' Politics' FROM tb_score GROUP BY userid

Note:

(1) SUM () is to be able to use GROUP BY to group according to userid, because there is only one record of subject= "language" corresponding to each userid, so the value of SUM () is equal to the value of score corresponding to that record.

If there are two records of userid = '001' and subject=' language', then the value of SUM () will be the sum of the two records. Similarly, the value using Max () will be the largest of the two records. But normally, a user has only one score for a subject, so you can use aggregate functions such as SUM (), MAX (), MIN (), AVG (), and so on, to achieve the effect of row transfer.

(2) IF (`roomt` = 'language', score,0) as a condition, that is, SUM (), MAX (), MIN () and AVG () are performed on the score fields of records in all subject=' languages. If there is no value for score, the default is 0.

3. Use SUM (IF ()) to generate column + WITH ROLLUP to generate summary row, and use IFNULL to display summary row title as Total.

SELECT IFNULL (userid,'total') AS userid,SUM (IF (`Secrett` = 'Chinese', score,0)) AS language, SUM (IF (`Secrett` = 'Mathematics', score,0)) AS Mathematics, SUM (IF (`Secrett` = 'English', score,0)) AS English, SUM (IF (`Secrett` = 'Politics', score,0)) AS Politics, SUM (IF (`Secrett` = 'total',score,0)) AS totalFROM (SELECT userid,IFNULL (`Secrett`,' total') AS `Secrett` SUM (score) AS score FROM tb_score GROUP BY userid, `roomt` WITH ROLLUP HAVING userid IS NOT NULL) AS A GROUP BY useridWITH ROLLUP

Running result:

4. Use SUM (IF ()) to generate column + UNION to generate summary row, and use IFNULL to display summary row title as Total.

SELECT userid,SUM (IF (`Secrett` = 'Chinese', score,0)) AS language, SUM (IF (`Secrett` = 'mathematics', score,0)) AS mathematics, SUM (IF (`Secrett` = 'English', score,0)) AS English, SUM (IF (`Secrett` = 'politics', score,0)) AS politics, SUM (score) AS TOTAL FROM tb_scoreGROUP BY useridUNIONSELECT 'TOTAL',SUM (IF (`Secrett` =' Chinese', score,0) AS language, SUM (IF (`Secrett` = 'mathematics', score)) AS 0) AS mathematics, SUM (IF (`polit` = 'English', score,0)) AS English, SUM (IF (`polit` = 'politics', score,0)) AS politics, SUM (score) FROM tb_score

Running result:

5. Use SUM (IF ()) to generate columns and generate results directly instead of using subqueries.

SELECT IFNULL (userid,'TOTAL') AS userid,SUM (IF (`Secrett` = 'Chinese', score,0)) AS language, SUM (IF (`Secrett` = 'Mathematics', score,0)) AS Mathematics, SUM (IF (`Secrett` = 'English', score,0)) AS English, SUM (IF (`polit` = 'Politics', score,0) AS Politics, SUM (score) AS TOTAL FROM tb_scoreGROUP BY userid WITH ROLLUP

Running result:

6. Dynamic, suitable for column uncertainty

SET @ EE='';select @ EE: = CONCAT (@ EE,'sum (if (subject=\', subject,'\', score,0)) as', subject,',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A; SET @ QQ = CONCAT ('select ifnull (userid,\' TOTAL\') as userid,',@EE,' sum (score) as TOTAL from tb_score group by userid WITH ROLLUP');-- SELECT @ QQ;PREPARE stmt FROM @ QQ;EXECUTE stmt;DEALLOCATE PREPARE stmt

Running result:

7. Merge field display: using group_concat ()

SELECT userid,GROUP_CONCAT (`roomt`, ":", score) AS score FROM tb_scoreGROUP BY userid

Running result:

Group_concat (), described in the manual: this function returns a string result with a non-null value of a connection from a group.

It is abstract and difficult to understand. In a popular way, it goes like this: group_concat () calculates which rows belong to the same group and displays the columns that belong to the same group. Which columns to return is determined by the function parameters (that is, the field names). Grouping must have a standard, that is, grouping according to the columns specified by group by.

Conclusion: the group_concat () function can well convert multiple rows belonging to the same group into a single column.

The above is about the content of this article on "how to convert rows to columns of mysql". I believe we all have some understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.