用SQL实现结果集的各种转换----将结果集反向转置为一列
来源:互联网 发布:怎么获得淘宝新品标签 编辑:程序博客网 时间:2024/05/05 21:38
之前的四篇关于用SQL将结果集进行转换的知识点,个人感觉可能就是行转为列时有点伤脑筋,但是找到方法后也还是可以解决的,其中有一点,对于每组中的第N行假设要一起排在结果集中的同一行,那么,那么就设法让每组中的第N行的某个列(假设为RN)的值为相同,其中用窗口函数Row_Number()方法可以实现,这样只要按照RN分组外加聚合函数(如Max)即可实现,然后在Case中判断RN列的值。而对于列转行则直接使用笛卡尔积,使得原表中的每一行(假设有M列)都具有M行,接下来要让这M行可以根据某两个列(其中一个进行分区,另外一个进行Row_Number() over(order by 列名) as RN),使得每行具有不同的一个RN值,这样就可以根据Case子句进行最后的组合。
一般情况下我们只需要利用窗口函数(row_number() over (partition by column1 order by column2))和Case子句进行行转列,而利用笛卡尔积和Case子句进行列转行.
下面介绍一种很特殊的情况,将所有的行转换为一列,将图1所示的结果集转换为图2所示的结果集。
图1 图2
根据前几章的介绍,我们这里要将图1的结果集转换为图2的结果集,其实就是将行转为列。这种特殊的行转列,我们用笛卡尔积和case子句即可解决。
首先将图1中的结果集进行笛卡尔运算,使得每行都变为相应的三行。如下SQL语句:
select ProvinceName,CityName,CityPopulation from CityTable,
(select top 3 ID from CityTable) as Table1
执行上面的SQL语句得到如图3所示的结果集
图3
现在我们要将每个ProvinceName进行分区,将每个ProvinceName里的所有CityName进行排序,如下SQL语句
with TableOne as
(
select ProvinceName,CityName,CityPopulation from CityTable,
(select top 3 ID from CityTable) as Table1
)
select *,row_number() over(partition by CityName order by CityName) as RN from TableOne
执行结果如图4所示
图4
接下来根据不同的RN值利用Case子句进行判断,即可得到结果
with TableOne as
(
select ProvinceName,CityName,CityPopulation from CityTable,
(select top 3 ID from CityTable) as Table1
)
,TableTwo as
(
select *,row_number() over(partition by CityName order by CityName) as RN from TableOne
)
select
case when RN=1 then ProvinceName
when RN=2 then CityName
when RN=3 then cast(CityPopulation as char(10))
else ''
end as '结果'
from TableTwo
order by ProvinceName
执行以上SQL语句即可得到如图5所示的结果
图5
该过程中遇到一些问题,如下:
一、错误信息:除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
SQL语句如下:
将第18行的order by ProvinceName放在了第9行,出错的原因:针对一个表的SELECT其实并不是返回一个表,而是一个游标;作为子查询,如果有order.....,规定必须这个子查询中含有一个top关键字。于是解决办法由两种:
第一:在该子查询中添加一个top关键字的语句:top 100 percent,即将第8行改为:
select top 100 percent*,row_number() over(partition by CityName order by CityName) as RN from TableOne
第二:在子查询中删除Order by 语句,将order by语句放在外部查询语句中。
二、下面的SQL语句中case子句里发生错误,信息如下:在将 nvarchar 值 '湖南' 转换成数据类型 int 时失败。
因为在上面的case子句中,ProvinceName和CityName是字符型的,而CityPopulation的类型是整型,所以要将CityPopulation转换为字符型,即将第13行代码改为when RN=3 then cast(CityPopulation as char(10))即可。
- 用SQL实现结果集的各种转换----将结果集反向转置为一列
- 将结果集反向转置为一列
- 将结果集反向转置为一列
- 用SQL实现结果集的各种转换----结果集转成一列
- 用SQL实现结果集的各种转换----结果集转成多列
- 用SQL实现结果集的各种转换----结果集转成多行
- SQL结果集转换--附多行转为一列
- 用SQL实现结果集的各种转换----附窗口函数
- 将JDBC结果集转换为XML
- 反向转置结果集
- 将数字格式的计算结果转换为汉字结果
- 将JDBC中的结果集转换为 model
- 将数据库中的结果集转换为json格式
- hibnernate 将sql结果集封装为实体对象
- hibernate,执行原生SQL并将结果转换为Map
- GROUP BY的另种使用方法:将结果集一列的内容作为一个单元格显示
- oracle 将查询结果中的一列合并为一个字符串
- 如果将Java中从数据库查询到的结果集转换为Json数组形式
- 死生契阔
- 逗号表达式
- PDC10与最后的恐慌者
- 1026: The Staircases
- PPTP服务器替代虚拟网卡(TAP)
- 用SQL实现结果集的各种转换----将结果集反向转置为一列
- 简单写个工具
- zoj 1331 Perfect Cubes
- P2P之NAT类型检测方法
- Ubuntu 10.04 下编译交叉工具链arm-linux-gcc 4.5.1
- 今天心情很好
- 呵呵,360杀毒弹出的
- 五虎大将
- Codeforces Beta Round #38 G Queue 动态树