1.我先建两个表 一个表示项目及级别 另一个表示项目最后一级中包含内容。两个表的数据如图
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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) )
2.新建一个函数 查找父节点
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
执行查询
select dbo.Prants(cProCode) cProName,* from yq_Qz_House where dbo.Prants(cProCode) like '%棋赛%'
结果如下图