首页 > 代码库 > SQL行转列
SQL行转列
一、使用场景
当系统中有汇总和明细的需求时,一般通过SQL来实现,汇总 就是 根据条件显示出全部的数据,明细 就是 一条汇总对应的详细信息。
行转列通常用于实现明细的时候。
二、举例实现
1.当表中不存在id时:
1). 新建表student,姓名(name)、学科(subject)、成绩(score)
create table student(name nvarchar(20),subject nvarchar(20),score int)
2). 插入数据
insert into student values(‘张三‘,‘语文‘,80),(‘张三‘,‘数学‘,90),(‘张三‘,‘英语‘,75) insert into student values(‘李四‘,‘语文‘,75),(‘李四‘,‘数学‘,93),(‘李四‘,‘英语‘,56) insert into student values(‘王五‘,‘语文‘,87),(‘王五‘,‘数学‘,67),(‘王五‘,‘英语‘,83)
3). 方式一实现:使用case when ... then ... end
select name, max(case subject when ‘语文‘ then score else 0 end) ‘语文‘, max(case subject when ‘数学‘ then score else 0 end) ‘数学‘, max(case subject when ‘英语‘ then score else 0 end) ‘英语‘, SUM(score) as ‘总分‘, cast(AVG(cast(score as decimal(10,2))) as decimal(10,2)) as ‘平均分‘from student group by name
4) 方式二实现:自己和自己关联
select a.name,a.score,b.score,c.score,(a.score+b.score+c.score) ‘总分‘,CAST((a.score+b.score+c.score)/3.0 as decimal(5,2)) ‘平均分‘ from student a,student b,student c where a.name = b.name and b.name = c.name and a.subject=‘语文‘ and b.subject=‘数学‘ and c.subject=‘英语‘
5) 方式三实现:使用pivot
select * from student pivot(max(score) for subject in (语文,数学,英语))a
2.当表中存在id时,
1). 新建表tb_student,序号(id)、姓名(name)、学科(subject)、成绩(score)
create table tab_student(id int,name nvarchar(20),subject nvarchar(20),score int)
2). 插入数据:
insert into tab_student values (1,‘张三‘,‘语文‘,76),(2,‘张三‘,‘数学‘,85),(3,‘张三‘,‘英语‘,67)insert into tab_student values (3,‘李四‘,‘语文‘,87),(4,‘李四‘,‘数学‘,32),(5,‘李四‘,‘英语‘,85)insert into tab_student values (6,‘王五‘,‘语文‘,83),(7,‘王五‘,‘数学‘,90),(8,‘王五‘,‘英语‘,80)
3) 方式一实现:使用 case when
select name,MAX(case subject when ‘语文‘ then score else 0 end) as ‘语文‘, MAX(case subject when ‘数学‘ then score else 0 end) as ‘数学‘, MAX(case subject when ‘英语‘ then score else 0 end) as ‘英语‘, SUM(score) ‘总数‘, cast(AVG(CAST(score as decimal(10,2))) as decimal(10,2)) ‘平均数‘ from tab_student group by name
4). 方式二实现:自己关联自己
select a.name,a.score,b.score,c.score,(a.score+b.score+c.score) ‘总分‘,CAST((a.score+b.score+c.score)/3.0 as decimal(5,2)) ‘平均分‘ from tab_student a,tab_student b,tab_student c where a.name = b.name and b.name = c.name and a.subject=‘语文‘ and b.subject=‘数学‘ and c.subject=‘英语‘
5). 方式三实现不了:pivot
select * from tab_student pivot( max(score) for subject in (语文,数学,英语))a
SQL行转列
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。