随笔 - 312, 文章 - 14, 评论 - 1393, 引用 - 0
数据加载中……

SQL Server2005杂谈(5):将聚合记录集逆时针和顺时针旋转90度

本文为原创,如需转载,请注明作者和出处,谢谢!

上一篇:SQL Server2005杂谈(4):在SQL Server2005中按列连接字符串的三种方法

    在输出统计结果时可能需要将列变成行,而将聚合结果(如count、sum)作为记录的第一行,先看如下的SQL语句:

declare @t table(name varchar(20))
insert @t
select 'abc' union all
select 'xxx' union all
select 'xxx' union all
select 'ttt'

select * from @t

    在执行上面的SQL语句后,会输出如图1所示的记录集。

图1

    上图显示的是一个普通的记录集,如果要统计name字段的每个值的重复数,需要进行分组,如下面的SQL如示:

select count(name) as c ,name  from @t group by name 


   
执行上面的SQL语句后的查询结果如图2所示。

图2

    如果我们有一个需求,需要如图3所示的聚合结果。


图3

      从图3可以看出,查询结果正好是图2的结果逆时针旋转90度,也就是说,name列的值变成了列名,而c列的值变成了第一行的记录。图2所示的c和name字段消失了。

    当然,要达到这个结果并不困难,看如下的SQL语句:

select (select count(name) from @t where name='abc'as abc, 
       (
select count(name) from @t where name='ttt'as xxx,
       (
select count(name) from @t where name='xxx'as ttt


    上 面的SQL语句会出输出如图3的查询结果。但这里有个问题,上面的SQL语句是枚举了name列所有可能的值,在本例中只有三个值 ('abc','ttt','xxx'),这非常好枚举,但如果有很多值,SQL语句会变得非常长,非常不利于编写。当然,可以通过编程的方式自动生成, 但最终结果仍然会生成很长的SQL语句。
   
为了解决这个问题,在SQL Server2005中提供了一个pivot函数,该函数可以很容易地输出如图3所示的记录集,如下面的SQL语句所示:

select * from @t pivot(count(name) for name in([abc] ,[ttt],[xxx]))


   
在执行上面的SQL语句同样可以获得图3所示的查询结果。实际上,pivot函数也起到了分组的作用。在使用pivot函数时应注意如下几点:

1. pivot函数需要指定聚合函数,如count、sum等,for关键字和聚合函数都要使用需要聚合的字段名,在本例中是name。

2. in关键字负责指定每组需要聚合的值,用[...]将这些值括起来。实际上,这些值也相当于我们第一种聚合方法中的where条件,例如,where name='abc'、where name='ttt',当然,这些值也是输出记录集的列名。

3. 在最后要为pivot函数起一个别名。

    虽然当要聚合的值很多时(或不确定),也需要动态生成SQL语句,但使用pivot函数的SQL语句却短很多。

    如 果我们还有一个需求,要将图3的结果变成图2的结果,也就是顺时针旋转90度,仍然以c和name作为字段名。也许方法很多,但SQL Server2005提供了一个unpivot函数,该函数是pivot函数的逆过程。也就是将记录集顺时针旋转90度,先看下面的SQL语句:
declare @t table(name varchar(20))
insert @t
select 'abc' union all
select 'xxx' union all
select 'xxx' union all
select 'ttt'
;
with tt as(
select * from @t pivot(count(name) for name in([abc] ,[ttt],[xxx])) p)
select * from tt

    上面的SQL语句将输出如图3所示的结果。如果将最后一条SQL语句(select * from tt)换成如下的SQL语句,将输出如图2所示的结果。

select * from tt  unpivot([c] for name in([abc] ,[xxx],[ttt])) p

    要注意的是,[c]中的c表示聚合结果列的字段名,name表示要聚合列的字段名,这两个值可以是任意满足字段名命名规则的字符串, [abc] ,[xxx],[ttt]分别是图3所示的记录集的字段名,这些值必须一致。执行下面的SQL语句将获得图4的输出结果。

select * from tt  unpivot([统计值] for 统计名 in([abc] ,[xxx],[ttt])) p



图4




Android开发完全讲义(第2版)(本书版权已输出到台湾)

http://product.dangdang.com/product.aspx?product_id=22741502



Android高薪之路:Android程序员面试宝典 http://book.360buy.com/10970314.html


新浪微博:http://t.sina.com.cn/androidguy   昵称:李宁_Lining

posted on 2009-02-21 15:38 银河使者 阅读(1833) 评论(3)  编辑  收藏 所属分类: SQL Serverdatabases 原创

评论

# re: SQL Server2005杂谈(5):将聚合记录集逆时针和顺时针旋转90度  回复  更多评论   

用with处理似乎不大对,实际上的数据库结构不可能是只有一列

比如:

declare @t table(name varchar(20),test datetime)
insert @t
select 'abc','1970' union all
select 'xxx','1971' union all
select 'xxx','1972' union all
select 'ttt','1973'
select * from @t
;
with tt as(
select * from @t pivot(count(name) for name in([abc] ,[ttt],[xxx])) p)
select * from tt

返回结果是:

1970-01-01 00:00:00.000 1 0 0
1971-01-01 00:00:00.000 0 0 1
1972-01-01 00:00:00.000 0 0 1
1973-01-01 00:00:00.000 0 1 0

显然不是我们想要的结果呢,如果把datetime改成是ntext,那么

根本都无法运行。

或者我对with的操作不大理解,您可以按照我的补充内容

给出相应正确的with操作吗?

2009-11-21 11:43 | lvjin

# re: SQL Server2005杂谈(5):将聚合记录集逆时针和顺时针旋转90度  回复  更多评论   

这样写的话:

SELECT *
FROM
(SELECT name
FROM @t) AS SourceTable
PIVOT
(
COUNT(name)
FOR name IN ([abc] ,[ttt],[xxx])
) AS PivotTable;

是可以得出正确结果:

1 1 2
2009-11-21 11:46 | lvjin

# re: SQL Server2005杂谈(5):将聚合记录集逆时针和顺时针旋转90度  回复  更多评论   

谢谢你的SQL杂谈五篇文章,学习了
2010-03-29 17:15 | y1sq1a

只有注册用户登录后才能发表评论。


网站导航: