mysql行列转换case_浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用

/*创建数据库*/

create database tmp

go

use tmp

go

/*创建数据库测试表*/

create table [scores]

(

[id] int identity(1, 1)

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 用法，行列转换*/

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 '语文' ,

avg(英语) as '英语'

from    scores pivot( avg(score) for subject in ( 语文, 英语 ) )as newscores

group by student

order by student asc

希望与广大网友互动？？

点此进行留言吧！