递归解决办法

递归
问题描述：
我有一个表结构如下：

MD1 MD2 MD3  
1 A1 A11  
2 A1 A12
3 A1 A13

1 A11 A21
2 A11 A22

1 A12 B21
2 A12 B22

1 B21 C21
2 B21 C22

1 D1 D11
2 D1 D12

1 D11 E11
2 D11 B22

1 E11 C21
2 E11 C22


然后想用SQL查C21 输出结果 A1 ,D1 或查B22 输入结果A1,D1，请高手指教,在2000的数据库里面如何写语句


[解决办法]

SQL code

--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([MD1] int,[MD2] varchar(3),[MD3] varchar(3))insert [tb]select 1,'A1','A11' union allselect 2,'A1','A12' union allselect 3,'A1','A13' union allselect 1,'A11','A21' union allselect 2,'A11','A22' union allselect 1,'A12','B21' union allselect 2,'A12','B22' union allselect 1,'B21','C21' union allselect 2,'B21','C22' union allselect 1,'D1','D11' union allselect 2,'D1','D12' union allselect 1,'D11','E11' union allselect 2,'D11','B22' union allselect 1,'E11','C21'go-->测试开始-->创建函数IF OBJECT_ID('dbo.XiaoAi') IS NOT NULL DROP FUNCTION dbo.XiaoAiGOCREATE FUNCTION dbo.XiaoAi(@ VARCHAR(20)) RETURNS @t TABLE(id VARCHAR(3), MD2 VARCHAR(3), MD3 VARCHAR(20),Level INT)AS BEGIN     DECLARE @level INT    SET @level=1    INSERT INTO @t SELECT *,@level FROM tb WHERE [MD3]=@    WHILE(@@ROWCOUNT>0)        BEGIN             SET @level=@level+1            INSERT INTO @t SELECT t.*,@level FROM tb AS t,@t AS a WHERE a.MD2=t.MD3 AND a.level=@level-1        ENDRETURN ENDGO-->调用函数SELECT MD2 from dbo.XiaoAi('C21') a where [Level]=(select max([Level]) from dbo.XiaoAi('C21'))-->结果/*MD2----A1D1(2 行受影响)*/