mysqlpivot函数怎么⽤_浅析SQL语句⾏列转换的两种⽅法
/*创建数据库*/
CREATE DATABASE tmp
go编程在线培训机构
USE tmp
go
/*创建数据库测试表*/
tcp ip的中文名称CREATE TABLE [Scores]
(
[ID] INT IDENTITY(1, 1)
php字符串处理函数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 ⽤法，⾏列转换*/sql update更新
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 '语⽂' ,
mysql怎么读英语>ipadpro参数
AVG(英语) AS '英语'
FROM    Scores PIVOT( AVG(Score) FOR Subject IN ( 语⽂, 英语 ) )as NewScores GROUP BY Student
ORDER BY Student ASC

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。