《MYSQL教程淺析SQL語句行列轉換的兩種方法 case...when與pivot函數的應用》要點：
本文介紹了MYSQL教程淺析SQL語句行列轉換的兩種方法 case...when與pivot函數的應用，希望對您有用。如果有疑問，可以聯系我們。

MYSQL必讀/*創建數據庫*/
?CREATE DATABASE tmp
?go
?USE tmp
?go

MYSQL必讀/*創建數據庫測試表*/
CREATE TABLE [Scores]
???? (
?????? [ID] INT IDENTITY(1, 1)
??????????????? PRIMARY KEY ,
?????? [Student] VARCHAR(20) ,
?????? [Subject] VARCHAR(30) ,
?????? [Score] FLOAT
???? )

MYSQL必讀go

MYSQL必讀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' )

MYSQL必讀/*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

MYSQL必讀/*2. pivot(聚合函數(要轉成列值的列名)
　????? for 要轉換的列
?　　??? in(目標列名)
?　　)*/

MYSQL必讀SELECT? Student AS '姓名' ,
???????? AVG(語文) AS '語文' ,
???????? AVG(英語) AS '英語'
FROM??? Scores PIVOT( AVG(Score) FOR Subject IN ( 語文, 英語 ) )as NewScores
?GROUP BY Student
?ORDER BY Student ASC

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5248.html