《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.vephp.com/jiaocheng/5248.html
