--递归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...