--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([MD1] int,[MD2] varchar(3),[MD3] varchar(3))
insert [tb]
select 1,'A1','A11' union all
select 2,'A1','A12' union all
select 3,'A1','A13' union all
select 1,'A11','A21' union all
select 2,'A11','A22' union all
select 1,'A12','B21' union all
select 2,'A12','B22' union all
select 1,'B21','C21' union all
select 2,'B21','C22' union all
select 1,'D1','D11' union all
select 2,'D1','D12' union all
select 1,'D11','E11' union all
select 2,'D11','B22' union all
select 1,'E11','C21'
go

-->测试开始
-->创建函数
IF OBJECT_ID('dbo.XiaoAi') IS NOT NULL DROP FUNCTION dbo.XiaoAi
GO
CREATE 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
        END
RETURN 
END
GO
-->调用函数
SELECT MD2 from dbo.XiaoAi('C21') a where [Level]=(select max([Level]) from dbo.XiaoAi('C21'))
-->结果
/*
MD2
----
A1
D1

(2 行受影响)

*/