SQL中递归查询

CREATE PROCEDURE [dbo].[SetColGroup](@sColItemCode VARCHAR(50),@ReportID VARCHAR(50) )
AS
-- 获取ItemCode对应的GroupCode,并更新至临时表中
BEGIN

DECLARE @sSQL VARCHAR(1000)
DECLARE @colGroupCode VARCHAR(50)
DECLARE @SuperColItemCode VARCHAR(50)
DECLARE @colSuperGroupCode VARCHAR(50)

--获取下一级别的数据（这里的SuperColItemCode实际上是下一级数据）
DECLARE curGroupCodeTmp CURSOR LOCAL FOR
SELECT c.ColGroupCode,b.SuperColItemCode,d.ColGroupCode AS ColSuperGroupCode
FROM dbo.ReportItemCross b
INNER JOIN dbo.ReportColItem c ON b.ColItemCode = c.ColItemCode
INNER JOIN dbo.ReportColItem d ON b.SuperColItemCode = d.ColItemCode
WHERE b.ColItemCode = @sColItemCode AND b.ReportID = @ReportID
OPEN curGroupCodeTmp
FETCH NEXT FROM curGroupCodeTmp
INTO @colGroupCode,@SuperColItemCode,@colSuperGroupCode
WHILE @@FETCH_STATUS = 0
BEGIN
--此处进行实际数据处理
IF(ISNULL(@colGroupCode,'')<>'' AND ISNULL(@colSuperGroupCode,'')<>'')
SET @sSQL = 'update ##tmp set ' + @colSuperGroupCode + ' = ''' + @SuperColItemCode
+ ''' where ' + @colGroupCode + ' = ''' + @sColItemCode + ''''
EXECUTE(@sSQL)
--根据条件判断是否结束递归
IF(ISNULL(@SuperColItemCode,'') <> '')
EXEC SetColGroup @SuperColItemCode,@ReportID
FETCH NEXT FROM curGroupCodeTmp
INTO @colGroupCode,@SuperColItemCode,@colSuperGroupCode
END

CLOSE curGroupCodeTmp
DEALLOCATE curGroupCodeTmp

END

with tt（select 语句1 --初始化
union all
select 语句2）
select * from tt
在select 语句2中from的表里关联with后面的表名tt就形成递推了。

给楼主例子
化解32层递归限制
http://blog.csdn.net/roy_88/article/details/2045842