T-SQL递归学习

参考文件：http://blog.csdn.net/jianxin160/article/details/7166379

结合项目修改如下：

表结构：

CREATE TABLE [dbo].[UserRelation](
[UserRelationId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[LeaderId] [bigint] NOT NULL,
 CONSTRAINT [PK_UserRelation] PRIMARY KEY CLUSTERED 

函数定义：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION GetTeamMember
(
@ID INT
)
RETURNS 
@tbChildren TABLE 
(
ID INT 
)
AS
BEGIN

WITH ChildrenCTE(ID)
AS
(
SELECT UserId FROM dbo.UserRelation WHERE LeaderId=@ID
UNION ALL
SELECT UserRelation.UserId FROM UserRelation INNER JOIN ChildrenCTE ON ChildrenCTE.ID = UserRelation.LeaderId
)
INSERT INTO @tbChildren  SELECT Id FROM ChildrenCTE

RETURN
END
GO

调用：

select top 100 * from vi_orderinfo v where exists( 
select 1 from dbo.GetTeamMember( 291 ) as a 
join dbo.UserCustomer b on a.Id = b.UserId where v.custno = b.customerno)


select top 100 * from vi_orderinfo v where v.custno in  ( 
select b.customerno from dbo.GetTeamMember( 291 ) as a 
join dbo.UserCustomer b on a.Id = b.UserId )

个人参考学习用