Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "what is the method of MySQL row conversion". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, let's take a look at our test table data and the expected query results:

Mysql > SELECT * FROM t_gaokao_score +-+ | id | student_name | subject | score | +-+ | 1 | Lin Lei er | Chinese | 148 | | 2 | Lin Leier | Mathematics | 150 | | 3 | Lin Lei er | English | 147 | 4 | Qiao Yingzi | Chinese | 121 | 5 | Qiao Yingzi | Mathematics | 106 | 6 | Qiao Yingzi | English | 146 | 7 | Fang Yifan | Chinese | 70 | 8 | Fang Yifan | | Mathematics | 90 | | 9 | Fang Yifan | English | 59 | | 10 | Fang Yifan | plus points | 200 | | 11 | Chen | Chinese | 109 | | 12 | Chen | Mathematics | 92 | 13 | Chen | English | 80 | + | -+ 13 rows in set (0.00 sec)

Take a look at the result of our row transfer:

+-+ | student_name | Chinese | Mathematics | English | Specialty bonus points | +- -- + + | Lin Lei er | 148 | 150 | 147 | 0 | | Qiao Yingzi | 121 | 106 | 146 | 0 | Fang Yifan | 70 | 90 | 59 | 200 | | Chen | 109 | 92 | 80 | 0 | +- -+ 4 rows in set (0.00 sec)

OK, let's take a look at how SQL is written.

First, row-to-column SQL writing

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

ELECT student_name, SUM (CASE `Secrett` WHEN 'Chinese' THEN score ELSE 0 END) as' Chinese', SUM (CASE `Secrett` WHEN 'mathematics' THEN score ELSE 0 END) as' math', SUM (CASE `beaut` WHEN 'English' THEN score ELSE 0 END) as' English', SUM (CASE `beaut`WHEN 'specialty bonus' THEN score ELSE 0 END) as' specialty bonus' FROM t_gaokao_score GROUP BY student_name

If SUM () is not used here, an error related to sql_mode=only_full_group_by will be reported, which can only be solved by using aggregate function in conjunction with group by or using distinct.

   in fact, added SUM () is to be able to use GROUP BY to group according to student_name, each student_name corresponding subject= "language" record, after all, there is only one, so the value of SUM () is equal to the value of score corresponding to that record. Of course, it can also be replaced with MAX ().

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

ELECT student_name, SUM (IF (`Secrett` = 'Chinese', score,0)) as' Chinese', SUM (IF (`except` = 'Mathematics', score,0)) as' Mathematics', SUM (IF (`except` = 'English', score,0)) as' English', SUM (IF (`except` = 'specialty bonus', score,0)) as' specialty bonus' FROM t_gaokao_score GROUP BY student_name

This method takes IF (subject=' language', score,0) as a condition, groups it by student_name, and performs SUM () operation on the score field of all records in subject=' language 'after grouping. If score has no value, it defaults to 0.

This method has the same principle as the case..when..then method, but it is more concise and clear, so it is recommended to use it.

What if the leader @ you asked you to add a total column to the result set?

Friendly reminder: when we deal with row-to-column data in our work, we try to add the total and average as far as possible, so as to facilitate the leader to consult, so as to save him from cycling BB you.

So, do you remember what your school report card was like? Do you usually look down or from the bottom up? Vote at the end of the article, come and have fun for everyone!

How to write: use SUM (IF ()) to generate columns, WITH ROLLUP to generate summary columns and rows, and use IFNULL to display summary row headers as total

SELECT IFNULL (total student_name,'') AS student_name, SUM (IF (`Secrett` = 'Chinese', score,0)) AS 'Chinese', SUM (IF (`except` = 'mathematics', score,0)) AS 'math', SUM (IF (`English'= 'English', score,0)) AS 'English', SUM (IF (`except` = 'specialty bonus', score,0)) AS 'specialty bonus' SUM (score) AS 'Total' FROM t_gaokao_scoreGROUP BY student_name WITH ROLLUP

Query results:

+-+ | student_name | Chinese | Mathematics | English | Specialty bonus points | Total number | +-- -+ | Qiao Yingzi | 121 | 106 | 146 | 0 | 373 | | Fang Yifan | 70 | 90 | 59 | 200 | 419 | | Lin Lei er | 148 | 150 | 147 | 0 | 445 | | | Chen | 113,116,80 | 0 | 309 | | Total | 452 | 462 | 432 | 1546 | +-+-+ 5 rows in set | 1 warning (0.00 sec) 3. The leader will change your needs again.

Let you turn the score into specific content display (excellent, good, ordinary, poor), more than 430 key universities, more than 400 points, 350 points and more ordinary universities, 350 or less brick, how to write?

   here we need case when nesting, look at high-end, in fact, it is just ordinary nesting. Find out the score of each subject after grouping on the first floor and replace it with a grade on the second floor.

SELECT student_name MAX (CASE subject WHEN 'language' THEN (CASE WHEN score-(select avg (score) from t_gaokao_score where subject=' language') > 20 THEN 'excellent' WHEN score-(select avg (score) from t_gaokao_score where subject=' language') > 10 THEN 'good' WHEN score-(select avg (score) from t_gaokao_score where subject=' language') > = 0 THEN 'ordinary' ELSE 'poor' END) END) as' Chinese' MAX (CASE subject WHEN 'mathematics' THEN (CASE WHEN score-(select avg (score) from t_gaokao_score where subject=' mathematics') > 20 THEN 'excellent' WHEN score-(select avg (score) from t_gaokao_score where subject=' mathematics') > 10 THEN 'good' WHEN score-(select avg (score) from t_gaokao_score where subject=' Mathematics') > = 0 THEN 'ordinary' ELSE 'poor' END) END) as' Mathematics' MAX (CASE subject WHEN 'English' THEN (CASE WHEN score-(select avg (score) from t_gaokao_score where subject=' English') > 20 THEN 'excellent' WHEN score-(select avg (score) from t_gaokao_score where subject=' English') > 10 THEN 'good' WHEN score-(select avg (score) from t_gaokao_score where subject=' English') > = 0 THEN 'ordinary' ELSE 'poor' END) END) as' English' SUM (score) as' total score', (CASE WHEN SUM (score) > 430 THEN 'key universities' WHEN SUM (score) > 400 THEN 'one' WHEN SUM (score) > 350 THEN 'ELSE' site moving bricks' END) as' result 'FROM t_gaokao_score GROUP BY student_name ORDER BY SUM (score) desc

Let's take a look at the output:

+-+ | student_name | Chinese | Mathematics | English | Total score | result | +-- -+ | Lin Leier | excellent | 445 | key University | | Fang Yifan | poor | 419 | one | | Qiao Yingzi | average | poor | excellent | 373 | Ordinary universities | | Chen | ordinary | average | poor | 309 | bricks moved on site | +-+-+ 4 rows in set (0.00 sec)

From the experience of people, honest children suffer the most, and they already know his mother's artistic style.

Appendix: create table structure & test data SQL

Table structure:

DROP TABLE IF EXISTS `troomgaokaoscore`; CREATE TABLE `troomgaokaoscore` (`id`gaokaoscore`) (`id` int (0) NOT NULL AUTO_INCREMENT, `student_ name` varchar (20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'student name', `account` varchar (20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'subject', `score`gaokao`score`, PRIMARY KEY (`id`) USING BTREE) ENGINE = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic

Import test data:

INSERT INTO `tweegaokaoscore`VALUES (1, 'Lin Leier', 'Chinese', 148), (2, 'Lin Lei er', 'Mathematics', 150), (3, 'Lin Lei er', 'English', 147), (4, 'Qiao Yingzi', 'Chinese', 121), (5, 'Qiao Yingzi', 'Mathematics', 106), (6, 'Qiao Yingzi', 'English', 146), (7, 'Fang Yifan' 'Chinese', 70), (8, 'Fang Yifan', 'Mathematics', 90), (9, 'Fang Yifan', 'English', 59), (10, 'Fang Yifan', 'plus points', 200), (11, 'Chen ', 'Chinese', 109), (12, 'Chen ', 'mathematics', 92), (13, 'Chen ', 'English', 80) This is the end of the content of "what is the method of transferring MySQL rows?" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.