DECLARE @str VARCHAR(MAX); SELECT @str = ''; WITH LiangLoveLan AS ( SELECT ID,ParentID,level = 1 FROM tb WHERE ID = 1998 UNION ALL SELECT B.ID,B.ParentID,A.level + 1 FROM LiangLoveLan AS A JOIN tb AS B ON A.ParentID = B.ID ) SELECT @str = @str + '/' + A.Name FROM tb AS A JOIN LiangLoveLan AS B ON A.ID = B.ID ORDER BY B.level DESC;SELECT STUFF(@str,1,1,'') AS [str];
select A.name+'/'+B.name+'/'+C.name+'/'+D.name from 表1 a join 表2 b on a.personid=b.personid join 表3 c on b.personid=c.personid join 表4 d on c.personid=d.personid where a.id=1998
select 4.name,3.name,2.name,1.name from table4 as 4 inner join table3 as 3 on(4.parentid = 3.id) inner join table2 as 2 on(3.parentid = 2.id) inner join table1 as 1 on(2.parentid = 1.id) 试试这行不行
--sql2000一句话无解--sql2005:;with szx as ( select id,name=cast(name as nvarchar(4000)) from tb where id=1998 union all select b.id,b.name+'/'+a.name from szx a join tb b on a.parentid=b.id ) select top 1 name from szx order by len(name) desc
--------------------------------- -- Author: htl258(Tony) -- Date : 2009-07-07 09:40:32 --------------------------------- --> 生成测试数据表-tbif not object_id('tb') is null drop table tb Go Create table tb([ID] int,[Name] nvarchar(5),[ParentID] int,[PERSONID] int) Insert tb select 1998,'1111',390,3 union all select 390,'电工基础',383,3 union all select 383,'基础知识',382,3 union all select 382,'调度员岗位',-1,3 Go --Select * from tb-->SQL查询如下: ;with t as ( select * from tb where parentid=-1 union all select tb.* from tb,t where tb.[ParentID]=t.ID ) select stuff((select '/'+name from t for xml path('')),1,1,'') as [path]/* path -------------------------------------------- 调度员岗位/基础知识/电工基础/1111(1 行受影响) */
eclare @D table (id int,name varchar(20),parentid int) insert into @D select 1998, '1111',390 declare @C table (id int,name varchar(20),parentid int) insert into @C select 390, '电工基础',383 declare @B table (id int,name varchar(20),parentid int) insert into @B select 383, '基础知识',382 declare @A table (id int,name varchar(20),parentid int) insert into @A select 382, '调度员岗位',-1select A.name + '/'+ B.name +'/'+C.name + '/'+D.name from @A as A inner join @B as B on B.parentid = A.id inner join @C as C on C.parentid = B.id inner join @D as D on D.parentid = C.id where A.id = 382 ---------------------------------- 调度员岗位/基础知识/电工基础/1111
灵活的 随便输入一个 ID 就可以找出 所有父级--------参考楼上大侠建表if not object_id('tb') is null drop table tb Go Create table tb([ID] int,[Name] nvarchar(5),[ParentID] int,[PERSONID] int) Insert tb select 1998,'1111',390,3 union all select 390,'电工基础',383,3 union all select 383,'基础知识',382,3 union all select 382,'调度员岗位',-1,3 Gosql 查询 declare @parentid int declare @str varchar(200) set @str='' declare @id int set @id=1998 while @id!=-1 begin select @id=parentid ,@str=name+'/'+@str from tb where id=@id end select substring(@str,1,len(@str)-1)---------------------------------- 调度员岗位/基础知识/电工基础/1111如果输入390 ----------------------------------调度员岗位/基础知识/电工基础
SELECT @str = '';
WITH LiangLoveLan AS
(
SELECT ID,ParentID,level = 1 FROM tb WHERE ID = 1998
UNION ALL
SELECT B.ID,B.ParentID,A.level + 1 FROM LiangLoveLan AS A
JOIN tb AS B
ON A.ParentID = B.ID
)
SELECT @str = @str + '/' + A.Name
FROM tb AS A
JOIN LiangLoveLan AS B
ON A.ID = B.ID
ORDER BY B.level DESC;SELECT STUFF(@str,1,1,'') AS [str];
from 表1 a join 表2 b on a.personid=b.personid
join 表3 c on b.personid=c.personid
join 表4 d on c.personid=d.personid
where a.id=1998
试试这行不行
(
select id,name=cast(name as nvarchar(4000)) from tb where id=1998
union all
select b.id,b.name+'/'+a.name from szx a join tb b on a.parentid=b.id
)
select top 1 name from szx order by len(name) desc
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-07 09:40:32
---------------------------------
--> 生成测试数据表-tbif not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[Name] nvarchar(5),[ParentID] int,[PERSONID] int)
Insert tb
select 1998,'1111',390,3 union all
select 390,'电工基础',383,3 union all
select 383,'基础知识',382,3 union all
select 382,'调度员岗位',-1,3
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select * from tb where parentid=-1
union all
select tb.* from tb,t where tb.[ParentID]=t.ID
)
select stuff((select '/'+name from t for xml path('')),1,1,'') as [path]/*
path
--------------------------------------------
调度员岗位/基础知识/电工基础/1111(1 行受影响)
*/
eclare @D table (id int,name varchar(20),parentid int)
insert into @D select 1998, '1111',390
declare @C table (id int,name varchar(20),parentid int)
insert into @C select 390, '电工基础',383
declare @B table (id int,name varchar(20),parentid int)
insert into @B select 383, '基础知识',382
declare @A table (id int,name varchar(20),parentid int)
insert into @A select 382, '调度员岗位',-1select A.name + '/'+ B.name +'/'+C.name + '/'+D.name
from @A as A
inner join @B as B
on B.parentid = A.id
inner join @C as C
on C.parentid = B.id
inner join @D as D
on D.parentid = C.id
where A.id = 382
----------------------------------
调度员岗位/基础知识/电工基础/1111
drop table tb
Go
Create table tb([ID] int,[Name] nvarchar(5),[ParentID] int,[PERSONID] int)
Insert tb
select 1998,'1111',390,3 union all
select 390,'电工基础',383,3 union all
select 383,'基础知识',382,3 union all
select 382,'调度员岗位',-1,3
Gosql 查询
declare @parentid int
declare @str varchar(200)
set @str=''
declare @id int
set @id=1998
while @id!=-1
begin
select @id=parentid ,@str=name+'/'+@str from tb where id=@id
end
select substring(@str,1,len(@str)-1)----------------------------------
调度员岗位/基础知识/电工基础/1111如果输入390
----------------------------------调度员岗位/基础知识/电工基础