mysql 行列转换 pivot_浅析SQL语句行列转换的两种方法 case…when与pivot函数的应用_MySQL...

bitsCN.com

/*创建数据库*/

CREATE DATABASE tmp

go

USE tmp

go

/*创建数据库测试表*/

CREATE TABLE [Scores]

(

[ID] INT IDENTITY(1, 1)

PRIMARY KEY ,

[Student] VARCHAR(20) ,

[Subject] VARCHAR(30) ,

[Score] FLOAT

)

go

TRUNCATE TABLE Scores

/*插入数据库测试数据信息*/

INSERT INTO Scores

( Student, Subject, Score )

VALUES ( ‘test001’, ‘语文’, ’90’ )

INSERT INTO Scores

( Student, Subject, Score )

VALUES ( ‘test001’, ‘英语’, ’85’ )

INSERT INTO Scores

( Student, Subject, Score )

VALUES ( ‘text002’, ‘语文’, ’90’ )

INSERT INTO Scores

( Student, Subject, Score )

VALUES ( ‘text002’, ‘英语’, ’80’ )

INSERT INTO Scores

( Student, Subject, Score )

VALUES ( ‘test003’, ‘语文’, ’95’ )

INSERT INTO Scores

( Student, Subject, Score )

VALUES ( ‘test003’, ‘英语’, ’85’ )

/*1. case when …….then else ….end 用法，行列转换*/

SELECT Student AS ‘姓名’ ,

MAX(CASE Subject

WHEN ‘语文’ THEN Score

ELSE 0

END) AS ‘语文’ ,–如果这个行是“语文”，就选此行作为列

MAX(CASE Subject

WHEN ‘英语’ THEN Score

ELSE 0

END) AS ‘英语’

FROM Scores

GROUP BY Student

ORDER BY Student

/*2. pivot(聚合函数(要转成列值的列名)

for 要转换的列

in(目标列名)

)*/

SELECT Student AS ‘姓名’ ,

AVG(语文) AS ‘语文’ ,

AVG(英语) AS ‘英语’

FROM Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScores

GROUP BY Student

ORDER BY Student ASC

bitsCN.com