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

fablefe

　　比如查询一个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语句