SQL Server递归查询和分页

圣诞快乐！好久没来写了，最近比较忙乱。今天放假在家休息了一天，就发一篇吧，以后还是要坚持写。

SQL Server 2005 中新支持了递归查询和row_number()函数。这些已经不是新话题了。只是在工作中遇到一个需求，要显示分类的新闻，并且支持分页。其中，新闻的分类是支持无限级的分类，因此这两个新特性就能够很好的完成这项工作。本来考虑到虚拟空间服务商提供的数据库可能是2000，不过现在看看很多都是2005的了，也就不再考虑兼容性的问题。

先看递归查询，递归查询特别适合获取层次类型的数据。例如，一个分类下的所有新闻（包括子分类）。SQL Server中可以利用公用表表达式(CTE)实现递归查询。其语法形式为：

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE

SELECT *
FROM cte_name

递归执行的语义如下：

1. 将 CTE 表达式拆分为定位点成员和递归成员。
2. 运行定位点成员，创建第一个调用或基准结果集 (T0)。
3. 运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。
4. 重复步骤 3，直到返回空集。
5. 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。

SQL的递归和普通语言的递归函数差不多，只是普通函数通常是返回一个值，SQL返回的是一张表，并且它将所有表的结果联合到一起返回。 下面看一个具体的例子，有如下两张表，分别表示新闻和新闻分类。新闻分类采用层次结构，利用ParentID关联。

CREATE TABLE [dbo].[Post](
	[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[Title] [nvarchar](500),
	[Content] [ntext],
	[CategoryID] [int] NOT NULL,
        [CreateTime] datetime DEFAULT(getdate()),
	[IsDelete] [bit] NULL 
) ON [PRIMARY] 
 
CREATE TABLE [dbo].[PostCategory](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](500),
	[Description] [nvarchar](500),
	[ParentID] [int] NULL, 
	[Level] [int] NOT NULL
) ON [PRIMARY]

我们写一个查询，返回所有属于某个分类的新闻（包括子分类）。例如返回ID是1的分类的所有新闻。首先我们要查找到分类号1的分类，然后递归的找出parentID是1的分类，最后和Post表进行连接即可。因此，查询代码如下:

with t as( 
select  postcategory.ID from postcategory where id=1
union all
select postcategory.ID from t join postcategory on t.ID=postcategory.parentID
)
select * from t join post on post.categoryID=t.ID
where isDelete=0

 

下面介绍row_number()函数，row_number()函数可以为返回的查询结果加上一列行号。其语法形式是:

ROW_NUMBER ()     OVER ( [ <partition_by_clause> ] <order_by_clause> )

其中，order by 子句指定根据什么顺序编号,partition by 子句可以按某一字段聚合，使得对那个字段的不同的值分别编号。说起来比较拗口，看一个例子就十分明了了。比较下面三个查询:

select  Title,[Content],categoryID from post
select Title,[Content],categoryID,row_number() over(order by [title]) from post
select Title,[Content],categoryID,row_number() over(partition by categoryID order by [title]) from post

其查询结果如下:

言归正传，现在把两者结合起来，就可以实现分页了:

with t as( 
select postcategory.ID from postcategory where id=1
union all
select postcategory.ID from t join postcategory on t.ID=postcategory.parentID
)
select * from(
select lite_post.ID,Title,[Content],row_number() over(order by CreateTime) as  rid  
from t join post on post.categoryID=t.ID
where isDelete=0  
)a  where rid between 2 and 3

这是一个例子，将最后的between的参数和id=1中的1替换成适当的参数，就写成一个存储过程供程序调用了。

 

最后是题外话，这篇文章里用了Syntax Highlighter 来给代码着色，这是一个开源的javascript语法着色器，非常好用。我还是第一次知道，以前我都用paste from visual studio。 这个插件比较有点局限。这个js着色器虽然不是那么精准，不过也够用了。