|
《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,'英语','85' )
?INSERT? INTO Scores
???????? ( Student,Score )
?VALUES? ( 'text002','80' )
?INSERT? INTO Scores
???????? ( Student,Score )
?VALUES? ( 'test003','95' )
?INSERT? INTO Scores
???????? ( Student,'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 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
