34,594
社区成员
发帖
与我相关
我的任务
分享
create table tb
(
parentid varchar(40),
id varchar(40),
lever int,
name varchar(64)
)
--第一层:
insert into tb values('2012','909',1,'气缸盖总成')
--第二层:
insert into tb values('909','929',2,'阀体')
insert into tb values('909','1709',2,'方螺母2')
--第三层:
insert into tb values('929','1711',3,'阀')
go
create function f_getid(@id varchar(40))
returns @t table(parentid varchar(40),id varchar(40),lever int,name varchar(64))
as
begin
insert @t select parentid,id,lever,name from tb where id = @id
while @@rowcount<>0
begin
insert @t select b.parentid,a.id,a.lever,a.name from tb a inner join @t b
on a.parentid = b.id and
not exists(select 1 from @t where id = a.id)
end
return
end
go
select * from dbo.f_getid('909')
drop function dbo.f_getid
drop table tb
/*
parentid id lever name
---------------------------------------- ---------------------------------------- ----------- ----------------------------------------------------------------
2012 909 1 气缸盖总成
2012 929 2 阀体
2012 1709 2 方螺母2
2012 1711 3 阀
(4 行受影响)
if object_id('t1') IS NOT NULL
drop table t1
go
create table t1
(
parentID varchar(40),
ID varchar(40),
Lever int,
[Name] varchar(64)
)
Insert into t1 values('2012','909',1,'气缸盖总成')
Insert into t1 values('909','929',2,'阀体')
Insert into t1 values('909','1709',2,'方螺母2')
Insert into t1 values('929','1711',3,'阀')
Insert into t1 values('255','654',1,'气缸盖总成')
Insert into t1 values('654','789',2,'阀体')
Insert into t1 values('654','7855',2,'方螺母2')
Insert into t1 values('789','3333',3,'阀')
create function [dbo].[GetRoot](@ID nvarchar(40)) returns nvarchar(40)
as
begin
declare @R as nvarchar(40)
declare @parentID as nvarchar(40)
declare @pLever as int
select @parentID=parentID,@pLever=Lever from t1 where ID=@ID
if @pLever=1
begin set @R=@parentID end
else
begin set @R=dbo.GetRoot(@parentID) end
return @R
end
select dbo.GetRoot(ID) as [Root],* from t1
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx
if object_id('tb_bookInfo') is not null drop table tb_bookInfo
go
create table tb_bookInfo(number int,name varchar(10),type int)
insert tb_bookInfo
select 1 ,'n1', 6 union all
select 2 ,'n2', 3
if object_id('tb_bookType') is not null drop table tb_bookType
go
create table tb_bookType(id int,typeName varchar(10),parentid int)
insert tb_bookType
select 1,'英语',0 union all
select 2,'生物',0 union all
select 3,'计算机',0 union all
select 4,'口语',1 union all
select 5,'听力',1 union all
select 6,'数据库',3 union all
select 7,'软件工程',3 union all
select 8,'SQL Server',6
select a.*,b.level from tb_bookInfo a,f_getC(3) b where a.type=b.id order by b.level
/*
number name type level
----------- ---------- ----------- -----------
2 n2 3 0
1 n1 6 1
(所影响的行数为 2 行)
*/
--查所有父结点
if object_id('f_getP') is not null drop function f_getP
go
create function f_getP(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,@l from tb_bookType a,@re b
where a.id=b.id and b.level=@l-1 and a.parentid<>0
end
update @re set level=@l-level
return
end
go
--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l from tb_bookType as a,@re as b
where b.id=a.parentid and b.level=@l-1
end
return
end
go
--查所有父子结点
if object_id('f_getAll') is not null drop function f_getAll
go
create function f_getAll(@id int)
returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,@l from tb_bookType a,@re b
where a.id=b.id and b.level=@l-1 and a.parentid<>0
end
update @re set level=@l-level
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l from tb_bookType as a,@re as b
where b.id=a.parentid and b.level=@l-1
end
return
end
go
--删除演示
drop table tb_bookInfo
drop table tb_bookType
drop function f_getP
drop function f_getC
drop function f_getAll
GO
--sqlserver2005的新方法
-- 建立演示环境
IF OBJECT_ID('[Dept]') IS NOT NULL
DROP TABLE [Dept]
GO
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO
--1、父-〉子
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO
--结果如下
/*
id parent_id name
----------- ----------- --------------------
6 4 MIS
7 6 UI
8 6 软件开发
9 8 内部开发
(所影响的行数为 4 行)
*/
--2、子-〉父
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'内部开发'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept WHERE name = @Dept_name
--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent FROM Dept where @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id
)
SELECT * FROM DEPTS
GO
--结果如下
/*
id parent_id name
----------- ----------- --------------------
9 8 内部开发
8 6 软件开发
6 4 MIS
4 0 业务部
(所影响的行数为 4 行)
*/
-- 删除演示环境
DROP TABLE Dept
if object_id('tempdb..#t') IS NOT NULL
drop table tempdb..#t
go
create table #t
(
parentID varchar(40),
ID varchar(40),
Lever int,
Name varchar(64)
)
--第一层:
Insert into #t values('2012','909',1,'气缸盖总成')
--第二层:
Insert into #t values('909','929',2,'阀体')
Insert into #t values('909','1709',2,'方螺母2')
--第三层:
Insert into #t values('929','1711',3,'阀')
--SELECT * FROM #t
;WITH t AS (
SELECT parent=parentid,* FROM #t WHERE lever=1
UNION ALL
SELECT parent,b.* FROM t a JOIN #t b ON a.id=b.parentID
)
SELECT parentid=parent,id,lever,name FROM t
/*
parentid id lever name
2012 909 1 气缸盖总成
2012 929 2 阀体
2012 1709 2 方螺母2
2012 1711 3 阀
*/
刚把贴子找出来你又发了if object_id('tempdb..#t') IS NOT NULL
drop table tempdb..#t
go
create table #t
(
parentID varchar(40),
ID varchar(40),
Lever int,
Name varchar(64)
)
--第一层:
Insert into #t values('2012','909',1,'气缸盖总成')
--第二层:
Insert into #t values('909','929',2,'阀体')
Insert into #t values('909','1709',2,'方螺母2')
--第三层:
Insert into #t values('929','1711',3,'阀')
--SELECT * FROM #t
;WITH t AS (
SELECT parent=parentid,* FROM #t WHERE lever=1
UNION ALL
SELECT parent,b.* FROM t a JOIN #t b ON a.id=b.parentID
)
SELECT parentid=parent,id,lever,name FROM t
/*
parentid id lever name
2012 909 1 气缸盖总成
2012 929 2 阀体
2012 1709 2 方螺母2
2012 1711 3 阀
*/
顶一下