MySQL multi-line variable multiple columns (derived form)

My article has compiled 2 common forms of MySQL multi-row variable columns: http://blog.csdn.net/rainyspring4540/article/details/50231435

Here is not to mention, the following is the more complex derivative form:

Table structure:

CREATE TABLE student (  name varchar) NOT NULL,--name  course varchar (a) NOT null,--account  score int,--score 
   
    bossevaluate varchar (,--)  ,--Family Assessment  of the societyevaluate varchar (20)--Social assessment--evaluation of the Familyevaluate  primary KEY (Name,course)); INSERT into student values (' Xiao Wang ', ' math ', ' ten ', ' A ', ' B ', ' C '); INSERT into student values (' Xiao Wang ' , ' language ', ' a ', ' a ', ' B ', ' C '), insert into student values (' Xiao Wang ', ' English ', ' ' A ', ' a ', ' B ', ' C '); INSERT into student values (' Floret ', ' math ', ' Ten ', ' A ', ' a ', ' a '); INSERT into student values (' Floret ', ' language ', ' + ', ' a ', ' a ', ' C '); INSERT into student values (' Floret ', ' English ', ' 10 ', ' A ', ' B ', ' C '); INSERT into student values (' Little tiger ', ' math ', ' + ', ' C ', ' B ', ' C '); INSERT into student values (' Tiger ', ' language ', ' ten ', ' A ', ' C ' , ' C '); INSERT into student values (' Little Tiger ', ' English ', ' ten ', ' A ', ' B ', ' C ');
   

Shaped like:

The following is the general generation of the presentation and SQL, posted out will not repeat:

SELECT name, Max (if (course= ' math ', score,0)) as ' Math ', max (if (course= ' language ', score,0)) as ' language ', Max (if (course= ' English ', score,0)) As ' English ' from ds_wjytest2.student Group by name;

What I'm using here is if it's not case, but the effect is almost the same;

Here are the more complex points of the derivative presentation and sql:

SELECT name, Max (if (course= ' math ', score,0)) as ' Math ', max (if (course= ' math ', bossevaluate,null)) as ' principal evaluation ', MAX (if (course= ') Math ', familyevaluate,null) as ' family Evaluation ', MAX (if (course= ' math ', societyevaluate,null)) as ' Social assessment ', Max (if (course= ' language ', score,0 ) as ' language ', Max (if (course= ' language ', bossevaluate,null)) as ' principal evaluation ', MAX (if (course= ' language ', familyevaluate,null)) as ' family Assessment ', Max ( if (course= ' language ', societyevaluate,null)) as ' Social assessment ', Max (if (course= ' English ', score,0)) as ' English ', Max (if (course= ' English ', Bossevaluate,null) as ' principal evaluation ', MAX (if (course= ' English ', familyevaluate,null)) as ' family Assessment ', Max (if (course= ' English ', Societyevaluate,null) as ' social assessment ' from Ds_wjytest2.student Group by name;

is not the feeling if function is very useful ... Some leaders like this lengthy report, by the way: if it is big data export such structure of data, it is best not to use this statement, if the amount of data is too large (10w), or similar to such a companion category field (where the subject is a category field) of the extension field (parental evaluation, social assessment, Little headmaster evaluation) too much will be the performance of the rapid decline;

I test to solve big data export such structure with paging export (with sort field), and each time paging reads the information of a part of the basic table is cached, because it is sorted, all the cache hit rate is very high; already hit the purge cache, the missing library reads this record, and read part of the information here to the cache (details after the single article ...) ）

MySQL multi-line variable multiple columns (derived form)