lzth.net
当前位置:首页 >> sql 递归 >>

sql 递归

sql 递归查询的方法: 方法一:T-SQL递归查询 with Dep as ( select Id,DeptCode,DeptName from Department where Id=1 union all select d.Id,d.DeptCode,d.DeptName from Dep inner join Department d on dep.Id = d.ParentDeptId ) select * ...

declare @b nvarchar(2) =1; with abc(id,b_PriorID,level)as ( select id,b_PriorID,0 level from #tt where b_PriorID =@b union all select a.id,a.b_PriorID,t.level+1 from #tt a inner join abc t on t.id=a.b_PriorID ) select * from ab...

With T As ( Select * From TB Where newNo=103 Union All Select TB.* From TB Inner Join T on TB.newNo=T.oldNo ) Select * From T

CREATE PROCEDURE up_delete @delid int AS declare @cnt int select count(1)=@cnt from table where parentid=@delid if (@cnt >0) begin exec up_delete @delid end delete from table where id=@delid go 调用方法 exec up_delete @id,会删...

给你一个例子,跟这个差不多: CREATE TABLE T(ID INT ,PID INT) INSERT INTO T SELECT 1,0 UNION ALL SELECT 2,0 UNION ALL SELECT 3,1 UNION ALL SELECT 4,1 UNION ALL SELECT 5,2 UNION ALL SELECT 6,4 UNION ALL SELECT 7,3 UNION ALL SELEC...

with tt(select 语句1 --初始化 union all select 语句2) select * from tt 在select 语句2中from的表里关联with后面的表名tt就形成递推了。

-- for M$SQL 2000+declare @result table (id int, pid int, idpath varchar(512))insert into @resultselect id,pid, cast(id as varchar)from thetablewhere id = @idwhile @@rowcounts > 0 begin insert into @result selectt a.id, a.pid, ...

比如有张表,结构如下:Example(ID int,ParentID int)取其某个ID为父节点的树结构的SQL如下:WITH Example_Table(ID,ParentID)AS( --取根节点放入临时表 SELECT ID,ParentID FROM Example WHERE ID = @ID --根据已取到的数据递归取其字节点的数据 ...

在SQL SERVER 2000 中你可以先一些自定义函数,或一些存储过程,实现递归: select level,TypeName from ProductType t START WITH t.ParentID=0 CONNECT BY PRIOR t.ProductTypeID= t.ParentID;

表结构: sale_area销售分区表 PK#area_id class_id gonghao prod_class产品分类表 PK#class_id class_name 问题sql语句: select distinct pa.class_id from prod_class As pa,prod_class As pb,sale_area where pb.class_id in(select sale_ar...

网站首页 | 网站地图
All rights reserved Powered by www.lzth.net
copyright ©right 2010-2021。
内容来自网络,如有侵犯请联系客服。zhit325@qq.com