SQL Server Select的递归查询-交叉表

比如查询一个Post的所有上级Post

Select的递归查询虽不常见,却令人着迷,它能够把垂直数据串联成水平数据,其语法如下:

SELECT @variable = @variable + d.column
FROM (Derived Table) as d

其中Derived Table常用Select子查询,所以给我的感觉更像一个循环,而不是递归(当然递归也是一种特殊的循环),Derived Table中的记录是自上而下逐行读取的,比如:

在[order]表shippingAddress列垂直方向上有记录:

shippingAddress

杭州

上海

南京

宁波

Select递归语句:

declare @shipping nvarchar(1024)
set @shipping = ''
SELECT @shipping = @shipping + d .ShippingAddress + ','
FROM (select distinct shippingAddress from [order]) as d          
select @shipping

结果:

上海,南京,宁波,

提升:利用select递归生成的数据串结合动态SQL可以生成复杂的交叉表。

1.列出想要生成的数据列

declare @shipping nvarchar(1024)
set @shipping = ''
SELECT @shipping = @shipping + 'sum(Quantity) as ' + d .ShippingAddress + ','
FROM (select distinct shippingAddress from [order]) as d          
set @shipping = 'select CargoID,' + @shipping + 'Sum(Quantity) as Total from [order] group by CargoID'
select @shipping
exec sp_executesql @shipping

结果:

CargoID 杭州 南京 宁波 上海

Tatol 1 9 9 9 9 9

2  2 2 2 2 2 3

11 11 11 11 11

2.我们的目标是统计不同的货物在不同地方发货的数量,所以做如下修改:

declare @shipping nvarchar(1024)
set @shipping = ''
SELECT @shipping = @shipping +
'sum(case ShippingAddress 
          when ''' + d.[Column] + ''' then Quantity
          else 0
     end) as ' + d.[Column] + ','
FROM (select distinct shippingAddress as [Column] from [order]) as d          
set @shipping = 'select CargoID,' + @shipping + 'Sum(Quantity) as Total from [order] group by CargoID'
select @shipping
exec sp_executesql @shipping

结果:

CargoID 杭州 南京 宁波 上海

Tatol 1 9 9 9 9 9

2  2 2 2 2 2 3

11 11 11 11 11

如果动态交叉表对于项目很重要,可以考虑使用Relational Application Companion(RAC),它为SQL Server编写生成交叉表存储过程,尽管如此,个人更推荐使用report service,更加清晰简单,可以回避复杂的Select语句

posted @   无很  阅读(429)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示