--递归id往下所有内容with temp ( URE_ID, URE_PARENT_ID,URE_NAME ,URE_TYPE)as(select URE_ID, URE_PARENT_ID,URE_NAME ,URE_TYPEfrom ASM_USER_REGIONwhere URE_PARENT_ID= 23union allselect a.URE_ID, a.URE_PARENT_ID,a.URE_NAME ,a.URE_TYPEfrom ASM_USER_REGION ainner join temp on a.URE_PARENT_ID = temp.URE_ID)select * from temp --递归id往上所有内容with temp ( URE_ID, URE_PARENT_ID,URE_NAME ,URE_TYPE)as(sel...
在sqlserver2005之前，要实现递归功能比较麻烦，比如可能会要用到临时表与while语句来循环。自sqlserver2005之后，新增了with as功能语法，即 公用表达式(CTE),让递归实现起来变的简单了。本章我们主要演示如何利用with as功能实现一个简单的递归功能。在这之前先看一下cte的语法：[ WITH <common_table_expression > [ ,...n ] ]<common_table_expression >::= expression_name [ ( column_name [ ,...n ] ) ] AS ...
--SQLServer 递归查询，主要用于建立树形结构IF OBJECT_ID('Categories') IS NOT NULLDROP TABLE Categories
GOCREATE TABLE Categories (CategoryID INT, CategoryName VARCHAR(20),ParentID INT )
GOINSERT INTO Categories(CategoryID, CategoryName, ParentID)SELECT 1, 'Books', NULL UNION ALLSELECT 2, 'SQL Server', 1 UNION ALLSELECT 3, 'ASP.NET', 1 UNION ALLSELECT 4, 'General', 2 UNION ALLSELECT 5, 'SSIS', 2 UNI...
--SQLServer 递归查询，主要用于建立树形结构IF OBJECT_ID(Categories) IS NOT NULLDROP TABLE Categories
GOCREATE TABLE Categories (CategoryID INT, CategoryName VARCHAR(20),ParentID INT )
GOINSERT INTO Categories(CategoryID, CategoryName, ParentID)SELECT 1, Books, NULL UNION ALLSELECT 2, SQL Server, 1 UNION ALLSELECT 3, ASP.NET, 1 UNION ALLSELECT 4, General, 2 UNION ALLSELECT 5, SSIS, 2 UNION ALLSELECT...
递归上下级部门，获取相关的部门单位部门表：
sql语句：with Temp1 as
(
select GroupID,G_ParentID from sys_Group where GroupID={当前部门GroupID}
union all
select sys_Group.GroupID,sys_Group.G_ParentID from Temp1 inner join sys_Group on Temp1.G_ParentID=sys_Group.GroupID ),
Temp2 as(
select GroupID,G_ParentID from sys_Group where GroupID={当前部门GroupID}
union all
select sys_Group.GroupID,sys_Group...
SQL Server使用递归查询数据标签：本文系统来源：http://www.cnblogs.com/yourchoose/p/4531221.html
http://msdn.microsoft.com/zh-cn/library/ms186243(v=SQL.90).aspx。
MsSql的语法如下：WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
递归执行的语义如下：将 CTE 表达式拆分为定位点成员和递归成员。
运行定位点...
--cteParents 父亲信息
with cteChildren(EmpID,ParentID)
AS
(
select EmpID,ParentID from Persons where parentid = 10171
union all
select t.EmpID,t.parentid from Persons as t
inner join cteChildren as c on t.ParentID = c.Id
),
cteParents(EmpID,ParentID)
AS
(
select EmpID,ParentID from Persons where Id= 10172
union all
select t.EmpID,t.parentid from Persons as t
inner join cteParents as c ...
rc
GODECLARE @nnn varchar(100)DECLARE cur1 cursor for
select [name] from sys.tables
where name LIKE ‘index_%‘drop table #Tmp --删除临时表#Tmp
create table #Tmp --创建临时表#Tmp
(kkk uniqueidentifier
);INSERT #Tmp VALUES(‘595C4D7D-B9E9-4E29-85F5-05C414CA961E‘)
INSERT #Tmp VALUES(‘92DFF3AF-7B65-43EF-AA64-069AF84896AA‘)
INSERT #Tmp VALUES(‘A3638B49-4CF0-4046-8349-0AB12560CDE6‘)
INSERT #Tmp...
select a.kmcode,a.kmname,a.pidkm xx sys_km a where kmcode in ( select t1.yskm from view_dj t1 where t1.swdjh=@bm)union all select k.kmcode,k.kmname,k.pidkm xx sys_km k inner join cte c on c.pidkm = k.kmcode ) 3.在通过cte和金额表关联把数据插入到临时表中代码如下
insert into #tmphjcx select * from ( select distinct a.kmcode,a.kmname,a.pidkm,
b.hjje,b.guoshui,b.dishui from cte aleft j...
SqlServer 省市县三级联动
三张表递归合并成一张表sql如下：
insert into table2(area_name,area_parent_id)select province,‘0‘ from provincesinsert into table2(area_name,area_parent_id)select city,c.id from cities ainner join provinces b on a.provinceid=b.provinceidinner join table2 c on b.province=c.area_nameinsert into table2(area_name,area_parent_id)select a.area,c.id from areas ainner join ...
drop table tb;
create table tb(id varchar(3) , pid varchar(3) , name varchar(10));
insert into tb values(‘001‘ , null , ‘广东省‘);
insert into tb values(‘002‘ , ‘001‘ , ‘广州市‘);
insert into tb values(‘003‘ , ‘001‘ , ‘深圳市‘) ;
insert into tb values(‘004‘ , ‘002‘ , ‘天河区‘) ;
insert into tb values(‘005‘ , ‘003‘ , ‘罗湖区‘);
insert into tb values(...
WITH cte AS ( SELECT a.FNUMBER,a.FMATERIALID AS MainId,b.FMATERIALID AS ChileID,CAST(b.FMATERIALID AS VARCHAR(max)) AS lev FROM t_eng_bom a JOIN dbo.T_ENG_BOMCHILD b ON a.fid =b.FID WHERE a.fid = ‘100150‘ UNION ALL SELECT d.FNUMBER,d.MainId,d.ChileID,CAST(lev AS VARCHAR(max))+ CAST(d.ChileID AS VARCHAR(max)) AS lev FROM cte c JOIN ( SELECT aa.FNUMBER,aa.FMATERIALID AS MainId,bbb.FMATERIALID ...
很多人可能想要查询整个树形表关联的内容都会通过循环递归来查...事实上在微软在SQL2005或以上版本就能用别的语法进行查询，下面是示例。
--通过子节点查询父节点WITH
TREE AS(
SELECT * FROM Areas
WHERE id = 6 -- 要查询的子 id UNION ALL
SELECT Areas.* FROM Areas, TREE
WHERE TREE.PId = Areas.Id
)
SELECT Area FROM TREE
--通过父节点查询子节点WITH
TREE AS(
SELECT * FROM Areas ...
GenerateHelpData
as
(select 0 as idunion allselect id+1 from GenerateHelpData where id<2047
)
select id from GenerateHelpData option (maxrecursion 2047);可以直接让CTE参数逻辑运算，也可以生成临时表，达到多次重用的目的，这样感觉是不是也很清爽？
1，生成连续数字（当然数字的起始值，间隔值都可以自定义）--生成连续数字
;with GenerateHelpData
as
(select 0 as idunion allselect id+1 from GenerateHelpData w...
--cteParents 父亲信息
with cteChildren(EmpID,ParentID)
AS
(
select EmpID,ParentID from Persons where parentid = 10171
union all
select t.EmpID,t.parentid from Persons as t
inner join cteChildren as c on t.ParentID = c.Id
),
cteParents(EmpID,ParentID)
AS
(
select EmpID,ParentID from Persons where Id= 10172
union all
select t.EmpID,t.parentid from Persons as t
inner join cteParents as c on t.Id= c.ParentID
)
--合并查询
select Idfrom cteChildren
UNION
select Idfrom cteParents