How does MySQL implement row-to-column hierarchical output? _ MySQL

Summary I haven't written SQL statements for a long time. I have a question in the Q & A today and I will study it. Problem link: about Mysql's hierarchical output problem scenario overview the scores recorded in the school. each person chooses different courses and will add courses later, so you do not need to take all courses as a column. Data Overview

I haven't written an SQL statement for a long time. I have a question in the Q & A today. I'll take a look at it.

Link: about Mysql's hierarchical output

Scenario overview

Scores are recorded in the school. each person chooses different courses and will add courses later. Therefore, you do not need to list all courses. For example, name + course is used as the Union primary key (the Union primary key is not required in some cases ). This article is based on MySQL. other databases may have some syntax differences.

Database table data:

Result after processing (row-to-column conversion ):

Method 1:

Max or Sum can be used here;

Note that in the second figure, when a student's score is missing, the output result is Null;

SELECTSNAME,MAX(CASE CNAMEWHEN 'JAVA' THENSCOREEND) JAVA,MAX(CASE CNAMEWHEN 'mysql' THENSCOREEND) mysqlFROMstdscoreGROUP BYSNAME;

You can add the Else statement in the first Case to solve this problem:

SELECTSNAME,MAX(CASE CNAMEWHEN 'JAVA' THENSCOREELSE0END) JAVA,MAX(CASE CNAMEWHEN 'mysql' THENSCOREELSE0END) mysqlFROMstdscoreGROUP BYSNAME;

Method 2:

SELECT DISTINCT  a.sname,(SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',(SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'FROM stdscore a

Method 3:

Drop procedureif exists sp_score; DELIMITER & create procedure sp_score () BEGIN # course name DECLAREcname_n VARCHAR (20); # DECLAREcount INT for all courses; # counter DECLAREi int default 0; # concatenate an SQL string SET @ s = 'SELECT sname'; SET count = (SELECTCOUNT (DISTINCT cname) FROMstdscore); WHILE I <count DOSET cname_n = (SELECTcnameFROMstdscoreGROUP by cname limit I, 1); SET @ s = CONCAT (@ s, ', SUM (CASE cname WHEN', '\ '', cname_n,' \'', 'Then score ELSE 0 END) ', 'as',' \ '', cname_n, '\''); SET I = I + 1; ENDWHILE; SET @ s = CONCAT (@ s, 'From stdscore group by sname'); # used for debugging # SELECT @ s; PREPARE stmtFROM @ s; EXECUTE stmt; END & CALL sp_score ();

The output of the processed results (row-to-column conversion) is classified:

Method 1:

Max or Sum can be used here;

Note that in the second figure, when a student's score is missing, the output result is Null;

SELECTSNAME, MAX (case cnamewhen 'Java' THEN (casewhen score-(select avg (SCORE) from stdscore where CNAME = 'Java')> 20 THEN 'excellent 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> 10 then' good 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> = 0 then' normal 'else' poor 'end) END) JAVA, MAX (case cnamewhen 'mysql' THEN (casewhen score-(select avg (SCORE) from stdscore where CNAME = 'Java')> 20 THEN 'excellent 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> 10 then' good 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> = 0 then' normal 'else' poor 'end) END) mysqlFROMstdscoreGROUP BYSNAME;

Method 2:

Select distinct. sname, (SELECT (casewhen score-(select avg (SCORE) from stdscore where CNAME = 'Java')> 20 then' excellent 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> 10 then' excellent 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> = 0 THEN 'normal 'else' poor 'end) FROM stdscore B WHERE. sname = B. sname AND B. CNAME = 'Java') AS 'Java', (SELECT (casewhen score-(select avg (SCORE) from stdscore where CNAME = 'Java')> 20 THEN 'excellent 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> 10 then' good 'When SCORE-(select avg (SCORE) from stdscore where CNAME = 'Java')> = 0 then' normal 'else' poor 'end) FROM stdscore B WHERE. sname = B. sname AND B. CNAME = 'mysql') AS 'mysql' FROM stdscore

Method 3:

Drop procedureif exists sp_score; DELIMITER & create procedure sp_score () BEGIN # course name DECLAREcname_n VARCHAR (20); # DECLAREcount INT for all courses; # counter DECLAREi int default 0; # concatenate an SQL string SET @ s = 'SELECT sname'; SET count = (SELECTCOUNT (DISTINCT cname) FROMstdscore); WHILE I <count DOSET cname_n = (SELECTcnameFROMstdscore group by cname limit I, 1); SET @ s = CONCAT (@ s, ', MAX (CASE cname when',' \ '', cname_n ,'\'', 'Then (casewhen score-(select avg (SCORE) from stdscore where CNAME = \ '', cname_n, '\')> 20 THEN \ 'excellent \ 'when score-(select avg (SCORE) from stdscore where CNAME = \ '', cname_n,' \ ')> 10 THEN \ 'good \ 'when score-(select avg (SCORE) from stdscore where CNAME = \ '', cname_n,' \ ')> = 0 THEN \ 'normal \ 'else \ 'poor \ 'end) END)', 'as', '\ '', cname_n ,'\''); SET I = I + 1; ENDWHILE; SET @ s = CONCAT (@ s, 'From stdscore GROUP BY sname'); # used for debugging # SELECT @ s; PREPARE stmtFROM @ s; EXECUTE stmt; END & CALL sp_score ();

Comparison and analysis of several methods. The first method is grouping, which processes each course separately.
The second method uses table join.
The third method uses the stored procedure, which can be the first or second method, which is dynamic. the number of all courses is calculated first, and then the course query is performed for each group. One of the biggest advantages of this method is that when a course is added, SQL statements do not need to be rewritten.

Summary About row-to-column and column-to-row

This concept seems easy to confuse. some people regard row-to-column conversion as column-to-row, while others regard column-to-row conversion as row-to-column conversion;

Here is a definition:

Row-to-column conversion:Deduplicate the data of a specific column (such as CNAME in this article) in the table as a column name (for example, "JAVA, mysql" in the query result line, which is output as a column name after processing );

Column to row:It can be said that the row-to-column inversion refers to the use of a specific column in the table (for example, the column name "JAVA, mysql" in the processing result in this article) as the value of the "CNAME" corresponding to each row of data;

Efficiency

I don't know any good methods or tools to generate analog data. please advise me and take the time to make a comparison;

Are there other better methods?

The several methods used in this article should have room for optimization, especially when using stored procedures, they will be more flexible and more powerful;

The grading in this article only provides a way of thinking. the grading method will be meaningless if the score difference is small;

If you have a better method, please kindly advise and be grateful!

Some requirements may not require primary keys.

Some requirements may not require a joint primary key, because a course may allow students to take the test multiple times, take the best score, or take the average score multiple times.