求一个SQL递归只要最有一级
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部-开发部-美工 **/
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.ffgocreate function ff(@id int) returns varchar(100)asbegin 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 @strendgoselect deptId,deptPid,deptName=dbo.ff(deptId) from dept dwhere not exists(select 1 from dept where deptPid=d.deptId)/*deptId deptPid deptName----------- ----------- ----------------------------------------------------------------1 0 财务部2 0 市场部5 3 IT部-测试部6 4 IT部-开发部-美工(4 行受影响)*/
[解决办法]
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,deptnamefrom ach twhere 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 行受影响)
[解决办法]
;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 dgroup 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 行受影响)*/
[解决办法]
;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 美工*/
[解决办法]
;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 美工*/
[解决办法]
--优化后的结果;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