SQL 使用with实现递归

今天遇到一个题目：

You are a database developer on an instance of SQL Server. You have a table named Category. The Category table contains a hierarchy of living things to the sub-species level. Each row in the Category table has a ParentCatlD value that corresponds to the item’s parent classification in the hierarchy. The parent-child relationship represented by the CatID and ParentCatlD columns is nested more than 20 levels. A sample of data in the Category is shown as follows:

Write query to calculate the children count for each record.

For example:

Living Things has 16 children

Invertebrates has 3 children

由于对sql了解不多，所以写了一个函数来实现递归。建表和插入语句不再赘述。

ALTER FUNCTION [dbo].[GetSonNumber]
(
    -- Add the parameters for the function here
    @catid int
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
 declare @sonnumber int
 set @sonnumber = 0
 
if exists (select g.catid from category as c, category as g
where c.catid = g.parentcatid and c.catid = @catid)

set @sonnumber += 
(case when (select COUNT(*)as sonnumber from category as c, category as g
where c.catid = g.parentcatid and c.catid = @catid) is null then 0 else
(select COUNT(*)as sonnumber from category as c, category as g
where c.catid = g.parentcatid and c.catid = @catid)
end)

set @sonnumber += 
(case when 
(select sum(dbo.GetSonNumber(g.catid)) from category as c, category as g
where c.catid = g.parentcatid and c.catid = @catid) IS null 
then 0
else
(select sum(dbo.GetSonNumber(g.catid)) from category as c, category as g
where c.catid = g.parentcatid and c.catid = @catid) end)

return @sonnumber


    -- Add the T-SQL statements to compute the return value here

END

后来了解到使用with可以非常方便的实现递归：

SQL Server  2005中提供了新的with关键字，用于指定临时命名的结果集，这些结果集称为公用表表达式（CTE）。该表达式源自简单查询，并且在SELECT、INSERT、UPDATE或DELETE
语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中，作为该语句的 SELECT
定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

其语法为：

 [ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name[ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )

使用with关键子的一个简单示例，以下代码将tb_loc表中数据源样输出：

WITH locs(id,name,parent)
AS
(
    SELECT * FROM tb_loc
)
SELECT * FROM locs

为了创建良好层次记录结构集，使用with关键字首先读取顶层记录，并且针对每一条顶层记录读取其子记录，直到读取到最底层级记录，最后将所有的记录组合起来，这里用到了UNION
ALL关键字，用于将多个查询结果组合到一个结果集中， 非常的方便。

with cate as 
( 
select * from Category c where c.CatID=@catid 
 union all 
select c.* from Category c join cate ct on c.ParentCatID=ct.CatID 
) 
select COUNT(1)-1 from cate