求一个SQL递归只要最有一级

KLL 2012-02-11 03:36:48

CREATE TABLE dept

(

    deptId int,

    deptPid int,

    deptName varchar(20)

)

INSERT INTO dept 

SELECT 1, 0, '财务部' UNION 

SELECT 2, 0, '市场部' UNION 

SELECT 3, 0, 'IT部' UNION 

SELECT 4, 3, '开发部' UNION 

SELECT 5, 3, '测试部' UNION 

SELECT 6, 4, '美工' 



SELECT * FROM dept

/**

要求结果如下:

deptId       deptPid         deptName         

1            0               财务部          

2            0               市场部          

5            3               IT部-测试部      

6            4               IT部-开发部-美工 

**/

...全文

205 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

一十七 2012-02-13

打赏
举报

取部门的级别是最高级别的（包括计算后的数据）

KLL 2012-02-12

打赏
举报

为什么deptpid都是0呢

黄_瓜 2012-02-11

打赏
举报

--优化后的结果

;with t as

(

    select deptId , deptPid , cast(deptName as varchar(100)) as  deptName from dept 

    union all

    select t.deptId , d.deptPid,cast(d.deptName+'-'+t.deptName as varchar(100)) 

    from t  inner join dept d on t.deptPid = d.deptId

) 

select * from t as a where deptPid=0 and not exists(select 1 from dept where deptPid=a.deptId)

order by deptId

yubofighting 2012-02-11

打赏
举报



;with cte as

(

	select distinct b.deptPid from dept a ,dept b where a.deptId=b.deptPid

)

,

cte1 as

(

	select * from dept where deptId not in (select * from cte)

)

select * from cte1



/*

1	0	财务部

2	0	市场部

5	3	测试部

6	4	美工

*/

yubofighting 2012-02-11

打赏
举报



;with cte as

(

	select distinct b.deptPid from dept a ,dept b where a.deptId=b.deptPid

)

,

cte1 as

(

	select * from dept where deptId not in (select * from cte)

)

select * from cte1



/*

1	0	财务部

2	0	市场部

5	3	测试部

6	4	美工

*/

黄_瓜 2012-02-11

打赏
举报

;with t as

(

    select deptId , deptPid = deptId,deptName from dept 

    union all

    select t.deptId , deptPid = d.deptPid,d.deptName from t inner join dept d on t.deptPid = d.deptId

) 

select deptId , deptPid,[path]=reverse(substring(reverse([path]) , charindex(',' , reverse([path])) + 1 , len([path])))   from

(

select deptId , deptPid,

       [path] = STUFF((SELECT '-' + deptName FROM t WHERE deptId = d.deptId order by t.deptId , t.deptPid FOR XML PATH('')) , 1 , 1 , '') 

from dept d

group by deptId , deptPid

) a where not exists(select 1 from dept where deptPid=a.deptId)

/*

deptId      deptPid     path

----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1           0           财务部-财务部

2           0           市场部-市场部

5           3           IT部-测试部-测试部

6           4           IT部-开发部-美工-美工



(4 行受影响)

*/

这个是2005 以及更高版本的不需要函数的方法

没有做优化的

AcHerat 2012-02-11

打赏
举报



CREATE TABLE dept

(

    deptId int,

    deptPid int,

    deptName varchar(max)

)

INSERT INTO dept 

SELECT 1, 0, '财务部' UNION 

SELECT 2, 0, '市场部' UNION 

SELECT 3, 0, 'IT部' UNION 

SELECT 4, 3, '开发部' UNION 

SELECT 5, 3, '测试部' UNION 

SELECT 6, 4, '美工' 

GO

/**

要求结果如下:

deptId       deptPid         deptName         

1            0               财务部          

2            0               市场部          

5            3               IT部-测试部      

6            4               IT部-开发部-美工 

**/



;with ach as

(

	select deptid,deptpid,deptname,0 as rid from dept

	union all

	select a.deptid,a.deptpid,b.deptname+'-'+a.deptname,b.rid+1 as rid

	from dept a join ach b on a.deptpid = b.deptid

)



select deptid,deptpid,deptname

from ach t

where not exists (select 1 from ach where deptid = t.deptid and rid > t.rid)

	and not exists (select 1 from ach where deptpid = t.deptid)



drop table dept



/**************



deptid      deptpid     deptname

----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1           0           财务部   

2           0           市场部   

5           3           IT部-测试部    

6           4           IT部-开发部-美工      



(4 行受影响)

黄_瓜 2012-02-11

打赏
举报



CREATE TABLE dept

(

    deptId int,

    deptPid int,

    deptName varchar(20)

)

INSERT INTO dept 

SELECT 1, 0, '财务部' UNION 

SELECT 2, 0, '市场部' UNION 

SELECT 3, 0, 'IT部' UNION 

SELECT 4, 3, '开发部' UNION 

SELECT 5, 3, '测试部' UNION 

SELECT 6, 4, '美工' 



if object_id('dbo.ff',N'FN') is not null drop function dbo.ff

go

create function ff(@id int) returns varchar(100)

as

begin

  declare @str as varchar(100)

  set @str = ''

  select @str = deptName from dept where deptId = @id

  while exists (select 1 from dept where deptId = @id and deptPid<>0)

    begin

      select @id = b.deptId , @str = b.deptName + '-' +@str  from dept a , dept b where a.deptId = @id and a.deptPid = b.deptId

    end

  return @str

end

go





select deptId,deptPid,deptName=dbo.ff(deptId) from dept d

where  not exists(select 1 from dept where deptPid=d.deptId)

/*

deptId      deptPid     deptName

----------- ----------- ----------------------------------------------------------------------------------------------------

1           0           财务部

2           0           市场部

5           3           IT部-测试部

6           4           IT部-开发部-美工



(4 行受影响)





*/