SQL row-to-column, column-changing methods

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/91/85/wKioL1j2vuCiSTLcAAAsXjQIOLM136.jpg-wh_500x0-wm_ 3-wmp_4-s_1989208398.jpg "title=" qq picture 20170419093438.jpg "alt=" Wkiol1j2vucistlcaaasxjqiolm136.jpg-wh_50 "/>

Title: There is an EMP, there are two fields: Name,chengji has three records, respectively, the language (name) 70 points, Math (name) 80, English (name) 58, please use a SQL query out these three records and display the conditions, Greater than or equal to 80 means excellent, greater than or equal to 60 means pass, less than 60 points to fail! Required display format as above!

First we create tables, add title Data!

CREATE TABLE EMP (NAME VARCHAR (), Chengji INT);

INSERT into EMP VALUES (' language ', 70), (' Math ', 80), (' English ', 58);

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/91/87/wKiom1j2wR6zupnhAABAFoIqvLA706.png-wh_500x0-wm_ 3-wmp_4-s_507187906.png "title=" qq picture 20170419094423.png "alt=" Wkiom1j2wr6zupnhaabafoiqvla706.png-wh_50 "/>

According to the requirements of the topic, we need to determine the results of these three rows, and then in the form of a column, which has a row-to-column operation.

The first kind of SQL notation:

SELECT MAX (case when name= ' language ' then (case is chengji>=80 then ' excellent ' when chengji<80 and chengji>=60 then ' passed ' Else ' failed ' end ' else ' end ' language ',

MAX (case when name= ' math ' then "chengji>=80 then ' excellent ' when chengji<80 and chengji>=60 then ' Pass ' ELSE ' Fail ' end ' ELSE ' end ' math ',

MAX (case when name= ' English ' then (case is chengji>=80 then ' excellent ' when chengji<80 and chengji>=60 then ' Pass ' ELSE ' Failed ' end ' ELSE ' End ' English ' from emp

The results of the implementation are as follows:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/91/88/wKiom1j2wx_ADv7ZAACGQcDPiHo897.png "title=" QQ picture 20170419095222.png "alt=" Wkiom1j2wx_adv7zaacgqcdpiho897.png "/>

Note: This usage of max (case) is used in the SQL above, and Max is here to take the title of not an empty string "in 3 judgments, Chinese, maths, English!

The second way to use the Group_concat function is also able to stitch out all the results to be written as follows:

SELECT group_concat (NAME SEPARATOR ' | ') From EMP UNION all SELECT

Group_concat (case is chengji>=80 then ' excellent ' when chengji<80 and chengji>=60 then ' Pass ' ELSE ' fail ' END separ Ator ' | ') From EMP

The results of the implementation are as follows:

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/91/87/wKioL1j2xPbxefwzAAB2E7FVth4984.png "title=" QQ picture 20170419100059.png "alt=" Wkiol1j2xpbxefwzaab2e7fvth4984.png "/>

In this way, the result is also acceptable, where the GROUP_CONCAT function is used to concatenate the class of the column, as a row! But this kind of result is a bit stiff feeling!

One thing to add: Here's the SQL notation we can see if we want to get through the first notation. We must know the contents of columns such as Chinese, maths, English, but the second we do not need to know! Here we come up with a way to spell out the SQL of the first method you want through the stored procedure, and then execute that, and then if we add columns to our table, or reduce the column, we won't get an error!

The wording is as follows:

DELIMITER $$

Use ' Yhtest ' $$

DROP PROCEDURE IF EXISTS ' yhtest ' $$

CREATE definer= ' root ' @ '% ' PROCEDURE ' yhtest ' ()

BEGIN

SET @sql = NULL;

SELECT

Group_concat (DISTINCT

CONCAT (

' MAX (case is name= ', ' \ ', emp.name, ' \ ', ' Then ' and ' Then ', ' "', '" ', ' ", '" ', ' ' when ' ", ' '" ') ' and ' ' when '

Chengji<80 and chengji>=60 then ', ' \ ', ' pass ', ' \ ', ' ELSE ', ' \ ', ' fail ', ' \ ', ' End ' ELSE ', ' \ ', ' ' ' ', ' end ' , ' \ ', emp.name, ' \ '

)

)

Into @sql

from EMP;

SET @sql = CONCAT (' Select ', @sql, ' from EMP ');

PREPARE STMT1 from @sql;

EXECUTE STMT1;

Deallocate PREPARE STMT1;

end$$

DELIMITER;

Invoke: Call Yhtest ();

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/91/8D/wKiom1j20nKh9jtuAAA_Q-vr4wI845.png-wh_500x0-wm_ 3-wmp_4-s_3749052473.png "title=" Qq20170419105829.png "alt=" Wkiom1j20nkh9jtuaaa_q-vr4wi845.png-wh_50 "/>

Insert a few data! We assume that we don't know how many subjects in advance!

INSERT into EMP VALUES (' physical ', 72), (' Sports ', 84);

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/91/8F/wKiom1j211WxucwvAABBljvNV5g784.png "title=" Qq20170419111911.png "alt=" Wkiom1j211wxucwvaabbljvnv5g784.png "/>

There's a problem here! Since we used the GROUP_CONCAT function in the stored procedure, this stitching function has a maximum stitching length of 1024 (default) over fixed length, truncation processing! Controlled by database parameter Group_concat_max_len! We can think of adjustments as needed!

Group_concat Adjust stitching symbol :group_concat (Name separator ' _ ')

Group_concat Sort: Group_concat (name order BY name Separator ' _ ')

SQL row-to-column, column-changing methods