Analysis of two methods of SQL statement row/column conversion case... application of when and limit function _ MySQL

Analysis of two methods of SQL statement row/column conversion case... the application of the when function and the limit function bitsCN.com

/* Create a database */
Create database tmp
Go
USE tmp
Go

/* Create a database Test table */
Create table [Scores]
(
[ID] int identity (1, 1)
Primary key,
[Student] VARCHAR (20 ),
[Subject] VARCHAR (30 ),
[Score] FLOAT
)

Go

Truncate table Scores
/* Insert database test data information */
Insert into Scores
(Student, Subject, Score)
VALUES ('test001', 'China', '90 ')
Insert into Scores
(Student, Subject, Score)
VALUES ('test001', 'English ', '85 ')
Insert into Scores
(Student, Subject, Score)
VALUES ('text002', 'China', '90 ')
Insert into Scores
(Student, Subject, Score)
VALUES ('text002', 'English ', '80 ')
Insert into Scores
(Student, Subject, Score)
VALUES ('test003 ', 'China', '95 ')
Insert into Scores
(Student, Subject, Score)
VALUES ('test003 ', 'English', '85 ')

/* 1. case when ...... then else... end usage, column and column conversion */
SELECT Student AS 'name ',
MAX (CASE Subject
WHEN 'China' THEN Score
ELSE 0
END) AS 'China', -- if this row is "Chinese", select this row AS the column
MAX (CASE Subject
WHEN 'English 'THEN Score
ELSE 0
END) AS 'English'
FROM Scores
Group by Student
Order by Student

/* 2. aggregate (aggregate function (column name to be converted into column value)
For column to be converted
In (target column name)
)*/

SELECT Student AS 'name ',
AVG (Chinese) AS 'China ',
AVG (English) AS 'English'
FROM Scores (AVG (Score) FOR Subject IN (Chinese, English) as NewScores
Group by Student
Order by Student ASC

BitsCN.com