SQL研究 - Apply

 

Apply是SQL Server 2005引入的一个新操作符，具有如下的基本语法：

 

select *

from tableA apply table_func(...)

 

Apply左边表的每一行输入右边的函数，该函数根据输入返回一个表，最后输入表的每一行与表结合起来，形成了结果集。

 有必要解释下表值函数，它是返回值为一个表的函数，请参考MSDN的相关文档。

 

现在先看看我们要解决的问题：

如果我们有一张表LocationLog，具有列Locations，内容示例如下：

JiangSu;BeiJing;Shanghai;

FuJian;ShanDong;JiangSu;

...

现在希望统计出各个省份出现的次数。

 

我们假设已经有这样一个函数，如后面附录所示，它能够将输入的一个长字符串分离成几个短字符串，如输入JiangSu;BeiJing;ShangHai，输出应该是：

 1 JiangSu

2 BeiJing

3 ShangHa

该函数名字是splitString。使用Apply，我们可以这样解决问题：

 

select subStr,count(*)

from LocationLog cross apply dbo.splitString(locationRecord）

group by subStr

 

结果如下：

BeiJing 1

FuJian 1

JiangSu 2

ShanDong 1

Shanghai 1

 

为了方便读者，我也写出splitString的简单实现。

CREATE FUNCTION dbo.splitString

(

 @strs nvarchar(1024),

 @ch nchar = ';'

)

RETURNS @tmp TABLE

(

 strId int identity(1,1),

 subStr nvarchar(1024)

)

AS

begin

 declare @pos int, @posX int

 set @pos = 1

 while( @pos < len(@strs) )

 begin

  set @posX = charindex(@ch,@strs,@pos)

  if( @posX = 0) set @posX = len(@strs) + 1

  insert into @tmp (subStr) values(substring(@strs,@pos,@posX-@pos))

  set @pos = @posX+1

 end

 RETURN

end

GO