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

MYSQL必讀/*創(chuàng)建數(shù)據(jù)庫*/
?CREATE DATABASE tmp
?go
?USE tmp
?go

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

MYSQL必讀go

MYSQL必讀TRUNCATE TABLE Scores
?/*插入數(shù)據(jù)庫測試數(shù)據(jù)信息*/
? 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 用法,行列轉(zhuǎn)換*/
?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(聚合函數(shù)(要轉(zhuǎn)成列值的列名)
　????? for 要轉(zhuǎn)換的列
?　　??? in(目標(biāo)列名)
?　　)*/

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

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5248.html