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 运营中心 武汉
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
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 cteid fid fname name
----------- ----------- ------------------------ ------------------------
25 NULL 运营中心 运营中心
107 25 运营中心 上海
106 25 运营中心 武汉
GO
/****** Object: StoredProcedure [dbo].[ADDUSER] Script Date: 08/06/2018 14:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOalter PROCEDURE ADDFileZi
asif 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;
GO
/****** Object: StoredProcedure [dbo].[ADDUSER] Script Date: 08/06/2018 14:10:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOalter PROCEDURE ADDFileZi
asif 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;
创建本地文件夹:
EXECUTE master.dbo.xp_create_subdir N'd:\database_bak\'