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

Overview

Long time no write SQL statements, today to see a question and answer, take a look.

Problem Link: Hierarchical output problem with MySQL

Scenario Brief

The school records the results, each person's choice course is different, and later will add the course, so do not need to take all the courses as a column. Data table data, such as the use of name + course as a joint primary key (some requirements may not need a federated primary key). This article is based on MySQL, and other databases will have a slightly different syntax.

database table Data:

Results after processing (row to column):

Method One:

You can use Max here, or you can use sum;

Attention to the second picture, when there is a student of a section of the result is missing, the output is null;

Selectsname,max (case Cnamewhen ' JAVA ' thenscoreend) java,max (case cnamewhen ' MySQL ' thenscoreend) Mysqlfromstdscoregroup Bysname;

You can resolve this issue by adding an else statement in the first case:

Selectsname,max (case Cnamewhen ' JAVA ' thenscoreelse0end) java,max (case cnamewhen ' MySQL ' thenscoreelse0end) Mysqlfromstdscoregroup Bysname;

Method Two:

Select DISTINCT  a.sname, (select score from Stdscore b WHERE a.sname=b.sname and B.cname= ' Java ') as ' Java ', (select SCO Re from Stdscore b WHERE a.sname=b.sname and b.cname= ' MySQL ') as ' MySQL ' from Stdscore a

Method Three:

DROP procedureif EXISTS sp_score;delimiter &&create PROCEDURE sp_score () begin# Course name Declarecname_n VARCHAR (20); #所有课程数量DECLAREcount INT; #计数器DECLAREi INT DEFAULT 0; #拼接SQL字符串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 ", ' \ ', Cname_n, ' \ ', ' then score ELSE 0 END) ', ' as ', ' \ ', Cname_n, ' \ '); SET i = i + 1; Endwhile; SET @s = CONCAT (@s, ' from Stdscore GROUP by Sname '); #用于调试 #select @s; PREPARE stmtfrom@s; EXECUTE stmt; End&&call Sp_score ();

Processed results (row to column) rated output:

Method One:

You can use Max here, or you can use sum;

Attention to the second picture, when there is a student of a section of the result is missing, the output is null;

Selectsname,max (case Cnamewhen ' Java ' then (Casewhen score – (select AVG (score) from Stdscore where cname= ' java ') > T HEN ' excellent ' when Score-(select AVG (score) from Stdscore where cname= ' JAVA ') > Ten then ' Good ' when Score-(select AVG (score) F Rom stdscore where cname= ' JAVA ') >= 0 Then ' normal ' ELSE ' poor ' end ' end) Java,max (case cnamewhen ' MySQL ' then (Casewhen score-(s Elect AVG (score) from Stdscore where cname= ' JAVA ') > "excellent" When Score-(select AVG (score) from Stdscore where CNA Me= ' java ') > ten then ' Good ' when Score-(select AVG (score) from Stdscore where cname= ' java ') >= 0 Then ' normal ' ELSE ' poor ' END) E ND) Mysqlfromstdscoregroup Bysname;

Method Two:

Select DISTINCT  a.sname, (select (Casewhen Score-(select AVG (score) from Stdscore where cname= ' JAVA ') > "good ' When Score-(select AVG (score) from Stdscore where cname= ' JAVA ') > Ten then ' Good ' when Score-(select AVG (score) from St Dscore where cname= ' java ') >= 0 Then ' normal ' ELSE ' poor ' END ' from Stdscore b where a.sname=b.sname and B.cname= ' Java ') as ' JAV A ', (select (Casewhen Score-(select AVG (score) from Stdscore where cname= ' JAVA ') > "excellent" When Score-(select Avg (score) from Stdscore where cname= ' java ') > ten then ' Good ' when Score-(select AVG (score) from Stdscore where cname= ' java ' ) >= 0 Then ' normal ' ELSE ' poor ' END ' from Stdscore b WHERE a.sname=b.sname and b.cname= ' MySQL ') as ' MySQL ' from Stdscore a

Method Three:

DROP procedureif EXISTS sp_score;delimiter &&create PROCEDURE sp_score () begin# Course name Declarecname_n VARCHAR (20); #所有课程数量DECLAREcount INT; #计数器DECLAREi INT DEFAULT 0; #拼接SQL字符串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 WH Ere cname=\ ', Cname_n, ' \ ') > then\ ' excellent ' when Score-(select AVG (score) from Stdscore where Cname=\ ', cname_n, ' \ ') &G T Ten then\ ' good ' when Score-(select AVG (score) from Stdscore where Cname=\ ', cname_n, ' \ ') >= 0 then\ ' normal \ ' else\ ' poor \ ' END ' E ND) ', ' as ', ' \ ', Cname_n, ' \ '); SET i = i + 1; Endwhile; SET @s = CONCAT (@s, ' from Stdscore GROUP by Sname '); #用于调试 #select @s; PREPARE stmtfrom@s; EXECUTE stmt; End&&call Sp_score ();

Comparative analysis of several methods the first used a grouping, and each course was treated separately.
The second method uses table joins.
The third is the use of stored procedures, which can actually be the first or second method of dynamic, first calculate the number of all courses, and then to each group for the course query. one of the biggest benefits of this approach is that when a new course is added, the SQL statement does not need to be rewritten.

Summary about row-to-column and column-changing careers

This concept seems easy to confuse, and some people interpret the line as a career change, and some people interpret it as a row-to-column;

Here's a definition:

row to column: the data of a particular column in a table (for example, CNAME in this article) is de-weighed as a column name (such as "Java,mysql" in the query result row, which is processed as the column name output);

column change: can be said to be the reversal of row and column, the table in a specific column (such as the column name in the results of this article "Java,mysql") as the row of data corresponding to the column "CNAME" value;

About efficiency

Do not know what good to generate simulation data methods or tools, trouble small partners recommend, spare me to do a contrast;

Is there any other better way to do it?

Several methods used in this paper should have the space of optimization, especially the use of stored procedures will be more flexible, more powerful;

The classification of this paper only gives a way of thinking, the method of grading if the student's performance difference is small, will lose meaning;

If the small partners have a better way, also please do not hesitate to enlighten, thank you!

Some requirements may not require a federated primary key

Some requirements may not require a federated primary key, as a course may allow students to take a number of tests, get the best results, or take a number of average results.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

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