sql 递归显示所有父节点

1.我先建两个表一个表示项目及级别另一个表示项目最后一级中包含内容。两个表的数据如图

CREATE TABLE [dbo].[yq_Project](
    [ID] [int] IDENTITY(1,1) primary key,
    [cCode] [varchar](32) NOT NULL,
    [cPriorCode] [varchar](32) NULL,
    [cName] [nvarchar](32) NULL,
    [cPersonName] [nvarchar](15) NULL,
    [cAddr] [nvarchar](20) NULL,
    [cTel] [varchar](20) NULL,
    [bHead] [bit] NULL,
    seq int )
alter table yq_project add cMemo nvarchar(128)
    
create table yq_Qz_House
(
id int identity(1,1) primary key,
cHuHao varchar(16),
cDongHao varchar(16),
cZuoLuo nvarchar(64),
cJieGou nvarchar(32),
cCengCi varchar(16),
cWeiZhi nvarchar(32),
cChaoXiang nvarchar(16),
iYuanLuo decimal(18,2),
dJunGongShiJian datetime,
cLeiBie nvarchar(16),
iZhuFang decimal(18,2),
iFengBiYangTai decimal(18,2),
iWeiFengYangTai decimal(18,2),
iGongYongFenTan decimal(18,2),
cProCode varchar(32)
)

View Code

2.新建一个函数查找父节点

if exists(select name from sysobjects where name='Prants')
 drop function Prants
go
create function Prants(@cCode varchar(32))
returns varchar(100)
as
begin
 declare @str varchar(100)
 set @str=''
 declare @num varchar(32)
 select @str= cName from yq_project  where cCode=@cCode
 if exists(select cPriorCode from yq_project where cCode=@cCode)
 begin
  select @num=cPriorCode from yq_project where cCode=@cCode
  set @str=@str+'/'+dbo.Prants(@num)
 end
 return @str
end

View Code

执行查询

select dbo.Prants(cProCode) cProName,*  from yq_Qz_House where dbo.Prants(cProCode) like '%棋赛%'

结果如下图