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语句