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

 

 

尚未了解：（使用With子句）

比如查询一个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	0	0	2	7	9
2	2	0	0	0	2
3	0	6	0	5	11

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