Home > Database > Mysql Tutorial > table 行转列的sql详解

table 行转列的sql详解

WBOY
Release: 2016-06-07 17:58:23
Original
1385 people have browsed it

tabele行转列的资料,网上搜一下很多。大家照着网上copy就可以实现自己想要的功能。但是大家在实现功能后是否想过行转列为什么要这样写?下面就以一个实例来分析, 希望对初学者有所帮助。

一、要求
1 创建数据表
CREATE TABLE [dbo].[StuScore](
[stuid] [int] NOT NULL,
[subject] [nvarchar](30) NULL,
[score] [decimal](5, 1) NULL
)
2 插入测试数据
stuid subject score
3 chinese 76.0
3 math 73.0
4 chinese 82.0
5 chinese 66.0
5 math 93.0
6 chinese 67.0
7 math 83.0
8 chinese 77.0
8 math 84.0
3 行转列后的结果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
二 、分析
1 行转列,一个重点就是怎么样知道有多少列,怎么样创建这些列?我们可以先把这个问题搁置,而假设这些列是已知的。 例如示例数据中,可以先假设subject的数据[chinese,math]是已知的,这样问题就简化了许多
2 当已知了chinese,math后,我们至少要先得到转换后的tabel结构
如下;
select stuid, 0 as chinese, 0 as math from dbo.StuScore
结果如下
stuid chinese math
3 0 0
3 0 0
4 0 0
5 0 0
5 0 0
6 0 0
7 0 0
8 0 0
8 0 0
3 接着就需要往这个数据集中去填充chinese, math的数据
select stuid,
case subject when 'chinese' then score else 0 end as chinese,
case subject when 'math' then score else 0 end as math
from dbo.StuScore
结果如下:
stuid chinese math
3 76.0 0.0
3 0.0 73.0
4 82.0 0.0
5 66.0 0.0
5 0.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 0.0
8 0.0 84.0
4 细心的读者会发现步骤3中的结果与我们想要的已经非常接近了,只需再做一个sum()处理,就OK了
select stuid,
sum(case subject when 'chinese' then score else 0 end ) as chinese,
sum(case subject when 'math' then score else 0 end ) as math
from dbo.StuScore group by stuid
得到的正是我们想要的结果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
是不是现在就已经完成了呢?答案是否定的。前面我们已经说过,是为了简化问题,在假设已经知道了subject数据的情况下,这么处理的,实际上subject的数据是可变的,未知的,接下来就是要解决这个问题了
5 要获取subject的数据其实很简单
select distinct subject from dbo.StuScore
获取以后怎样得到case subject when 'chinese' then score else 0 end 这种语句?
可以根据subject的值去动态的组sql语句
看下面的一段代码
declare @sql varchar(2000)
set @sql=''
select @sql =@sql+ ',case subject when '''+subject+''' then 1 else 0 end as ' + subject
from (select distinct subject from dbo.StuScore) as sub
print @sql
message打印的信息如下:
,case subject when 'chinese' then 1 else 0 end as chinese,case subject when 'math' then 1 else 0 end as math
6 最后我们就需要将前面步骤综合起来,得到最终的sql
declare @sql varchar(2000)
set @sql='select stuid'
select @sql =@sql+ ',sum(case subject when '''+subject+''' then score else 0 end) as ' + subject
from (select distinct subject from dbo.StuScore) as sub
set @sql=@sql + ' from dbo.StuScore group by stuid'
exec(@sql)
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
至此,整个分析过程和结果就都出来了。
初试写文章, 多包涵,指正。
Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template