SQLserver递归查询

[花样小星]

id name date father uid unionid
25 运营中心 2017-10-01 801 1 1
107 上海 2017-10-01 25 1 1
106 武汉 2017-10-01 25 1 1

怎么使用递归显示出
25 运营中心 上海
26 运营中心 武汉

[二月十六]

这个结果不用递归就可以查询出来

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(24),[date] Date,[father] int,[uid] int,[unionid] int)
Insert #T
select 25,N'运营中心','2017-10-01',801,1,1 union all
select 107,N'上海','2017-10-01',25,1,1 union all
select 106,N'武汉','2017-10-01',25,1,1
Go
--测试数据结束
Select a.id,a.name,b.name from #T a JOIN #T b ON a.id = b.father

[卖水果的net]

一共就两级？ 别用递归了。

[听雨停了]

WITH cte AS (
Select id,NULL AS fid,name AS fname,NAME  from #tab WHERE id=25
UNION ALL
SELECT b.id,a.id,a.name,b.name
FROM cte  AS a
INNER JOIN #tab AS b ON a.id=b.father
)
SELECT id,fid,fname,name FROM cte

id          fid         fname                    name
----------- ----------- ------------------------ ------------------------
25          NULL        运营中心                     运营中心
107         25          运营中心                     上海
106         25          运营中心                     武汉

[花样小星]

USE [master]
GO
/****** Object: StoredProcedure [dbo].[ADDUSER] Script Date: 08/06/2018 14:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE ADDFileZi
as

if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
create table #T3([id] int,[name] nvarchar(24),[father] int)
Insert #T3
select * from OPENQUERY
(MYSQL_OA_DB,'select id,name,father from toa_department')

declare @departmentname varchar(50),@departmentnamezi varchar(50),@sqldepartmentnamezi varchar(100)


-- 声明游标
DECLARE C_ADDFilezi CURSOR FAST_FORWARD FOR
--查询出

Select a.name,b.name as namezi from #T3 a JOIN #T3 b ON a.id = b.father


OPEN C_ADDFilezi;

-- 取第一条记录
FETCH NEXT FROM C_ADDFilezi INTO @departmentname,@departmentnamezi;

WHILE @@FETCH_STATUS=0
BEGIN

set @sqldepartmentnamezi='md d:\HelensFile\'+@departmentname+'\'+@departmentnamezi+''
exec xp_cmdshell @sqldepartmentnamezi

-- 取下一条记录
FETCH NEXT FROM C_ADDFilezi INTO @departmentname,@departmentnamezi;
END

-- 关闭游标
CLOSE C_ADDFilezi;

-- 释放游标
DEALLOCATE C_ADDFilezi;

引用 3 楼 qq_37170555 的回复:

WITH cte AS (
Select id,NULL AS fid,name AS fname,NAME  from #tab WHERE id=25
UNION ALL
SELECT b.id,a.id,a.name,b.name
FROM cte  AS a
INNER JOIN #tab AS b ON a.id=b.father
)
SELECT id,fid,fname,name FROM cte

id          fid         fname                    name
----------- ----------- ------------------------ ------------------------
25          NULL        运营中心                     运营中心
107         25          运营中心                     上海
106         25          运营中心                     武汉

[花样小星]

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[ADDUSER]    Script Date: 08/06/2018 14:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE ADDFileZi
as

if not object_id(N'Tempdb..#T3') is null
    drop table #T3
Go
create table #T3([id] int,[name] nvarchar(24),[father] int)
Insert #T3
select * from OPENQUERY 
(MYSQL_OA_DB,'select id,name,father from toa_department')

declare @departmentname varchar(50),@departmentnamezi varchar(50),@sqldepartmentnamezi varchar(100)

   
-- 声明游标
DECLARE C_ADDFilezi CURSOR FAST_FORWARD FOR
--查询出

Select a.name,b.name as namezi from #T3 a JOIN #T3 b ON a.id = b.father

    
OPEN C_ADDFilezi;

-- 取第一条记录
FETCH NEXT FROM C_ADDFilezi INTO @departmentname,@departmentnamezi;

WHILE @@FETCH_STATUS=0
BEGIN
   
	set @sqldepartmentnamezi='md d:\HelensFile\'+@departmentname+'\'+@departmentnamezi+''
	exec xp_cmdshell @sqldepartmentnamezi
     
    -- 取下一条记录
    FETCH NEXT FROM C_ADDFilezi INTO  @departmentname,@departmentnamezi;
END

-- 关闭游标
CLOSE C_ADDFilezi;

-- 释放游标
DEALLOCATE C_ADDFilezi;

[花样小星]

引用 1 楼 sinat_28984567 的回复:

这个结果不用递归就可以查询出来

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(24),[date] Date,[father] int,[uid] int,[unionid] int)
Insert #T
select 25,N'运营中心','2017-10-01',801,1,1 union all
select 107,N'上海','2017-10-01',25,1,1 union all
select 106,N'武汉','2017-10-01',25,1,1
Go
--测试数据结束
Select a.id,a.name,b.name from #T a JOIN #T b ON a.id = b.father

大神帮我看一下 我怎么可以实现递归在本地磁盘创建文件夹呢

[吉普赛的歌]

不明白你想实现的效果， 你可以把你的最终的结果说出来， 这样大家更容易领会：
创建本地文件夹：

EXECUTE master.dbo.xp_create_subdir N'd:\database_bak\'

[RINK_1]

引用 6 楼 me_meihuo 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:] 这个结果不用递归就可以查询出来

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(24),[date] Date,[father] int,[uid] int,[unionid] int)
Insert #T
select 25,N'运营中心','2017-10-01',801,1,1 union all
select 107,N'上海','2017-10-01',25,1,1 union all
select 106,N'武汉','2017-10-01',25,1,1
Go
--测试数据结束
Select a.id,a.name,b.name from #T a JOIN #T b ON a.id = b.father

大神帮我看一下 我怎么可以实现递归在本地磁盘创建文件夹呢[/quote] 你也可以用动态语句进行拼接后，再执行来批量生成文件夹。