/* 标题:SQL SERVER 2000中查询指定节点及其所有父节点的函数(表格形式显示) 作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间:2008-05-12 地点:广东深圳 */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('006' , '003' , '福田区') insert into tb values('007' , '003' , '宝安区') insert into tb values('008' , '007' , '西乡镇') insert into tb values('009' , '007' , '龙华镇') insert into tb values('010' , '007' , '松岗镇') go--查询指定节点及其所有父节点的函数 create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3)) as begin insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null while @@ROWCOUNT > 0 begin insert into @t_level select @id select @id = pid from tb where id = @id and pid is not null end return end go--调用函数查询002(广州市)及其所有父节点 select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id /* id pid name ---- ---- ---------- 001 NULL 广东省 002 001 广州市(所影响的行数为 2 行) */--调用函数查询003(深圳市)及其所有父节点 select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id /* id pid name ---- ---- ---------- 001 NULL 广东省 003 001 深圳市(所影响的行数为 2 行) */--调用函数查询008(西乡镇)及其所有父节点 select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id /* id pid name ---- ---- ---------- 001 NULL 广东省 003 001 深圳市 007 003 宝安区 008 007 西乡镇(所影响的行数为 4 行) */drop table tb drop function f_pid@@ROWCOUNT:返回受上一语句影响的行数。 返回类型:integer。 注释:任何不返回行的语句将这一变量设置为 0 ,如 IF 语句。 示例:下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777' IF @@ROWCOUNT = 0 print 'Warning: No rows were updated'结果:(所影响的行数为 0 行) Warning: No rows were updated /* 标题:SQL SERVER 2005中查询指定节点及其所有父节点的方法(表格形式显示) 作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间:2010-02-02 地点:新疆乌鲁木齐 */create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10)) insert into tb values('001' , null , N'广东省') insert into tb values('002' , '001' , N'广州市') insert into tb values('003' , '001' , N'深圳市') insert into tb values('004' , '002' , N'天河区') insert into tb values('005' , '003' , N'罗湖区') insert into tb values('006' , '003' , N'福田区') insert into tb values('007' , '003' , N'宝安区') insert into tb values('008' , '007' , N'西乡镇') insert into tb values('009' , '007' , N'龙华镇') insert into tb values('010' , '007' , N'松岗镇') goDECLARE @ID VARCHAR(3)--查询ID = '001'的所有父节点 SET @ID = '001' ;WITH T AS ( SELECT ID , PID , NAME FROM TB WHERE ID = @ID UNION ALL SELECT A.ID , A.PID , A.NAME FROM TB AS A JOIN T AS B ON A.ID = B.PID ) SELECT * FROM T ORDER BY ID /* ID PID NAME ---- ---- ---------- 001 NULL 广东省(1 行受影响) */--查询ID = '002'的所有父节点 SET @ID = '002' ;WITH T AS ( SELECT ID , PID , NAME FROM TB WHERE ID = @ID UNION ALL SELECT A.ID , A.PID , A.NAME FROM TB AS A JOIN T AS B ON A.ID = B.PID ) SELECT * FROM T ORDER BY ID /* ID PID NAME ---- ---- ---------- 001 NULL 广东省 002 001 广州市(2 行受影响) */--查询ID = '003'的所有父节点 SET @ID = '003' ;WITH T AS ( SELECT ID , PID , NAME FROM TB WHERE ID = @ID UNION ALL SELECT A.ID , A.PID , A.NAME FROM TB AS A JOIN T AS B ON A.ID = B.PID ) SELECT * FROM T ORDER BY ID /* ID PID NAME ---- ---- ---------- 001 NULL 广东省 003 001 深圳市(2 行受影响) */--查询ID = '009'的所有父节点 SET @ID = '009' ;WITH T AS ( SELECT ID , PID , NAME FROM TB WHERE ID = @ID UNION ALL SELECT A.ID , A.PID , A.NAME FROM TB AS A JOIN T AS B ON A.ID = B.PID ) SELECT * FROM T ORDER BY ID /* ID PID NAME ---- ---- ---------- 001 NULL 广东省 003 001 深圳市 007 003 宝安区 009 007 龙华镇(4 行受影响) */drop table tb--注:除ID值不一样外,四个SQL语句是一样的。 把上面的做成子查询,然后select count(1) from (...) t
if object_id('tempdb.dbo.#t') is not null drop table #t create table #t (id int,name varchar(1),pid int) insert into #t select 1,'a',0 union all select 2,'b',1 union all select 3,'c',1 union all select 4,'d',2with cte as ( select * from #t where id=4 union all select #t.* from cte join #t on cte.pid=#t.id ) --select * from cte select count(*) from cte where id<>4 ----------- 2(1 行受影响)
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(1),[pid] int) insert [tb] select 1,'a',0 union all select 2,'b',1 union all select 3,'c',1 union all select 4,'d',2--------------------------------查询开始------------------------------DECLARE @s VARCHAR(10) SET @s='c' if object_id('tempdb..#') is not null drop table #; SELECT id, pid,[name] INTO # FROM tb WHERE [name]=@s WHILE @@ROWCOUNT>0 BEGIN INSERT INTO # SELECT t.id, t.pid,t.[name] FROM tb AS t INNER JOIN # AS a ON t.id=a.pid AND t.id NOT IN(SELECT ID FROM #) END SELECT count(*) FROM # /* ----------- 2(1 行受影响) */
/*
标题:SQL SERVER 2000中查询指定节点及其所有父节点的函数(表格形式显示)
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-05-12
地点:广东深圳
*/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('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有父节点的函数
create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
as
begin
insert into @t_level select @id
select @id = pid from tb where id = @id and pid is not null
while @@ROWCOUNT > 0
begin
insert into @t_level select @id
select @id = pid from tb where id = @id and pid is not null
end
return
end
go--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市(所影响的行数为 2 行)
*/--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市(所影响的行数为 2 行)
*/--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市
007 003 宝安区
008 007 西乡镇(所影响的行数为 4 行)
*/drop table tb
drop function f_pid@@ROWCOUNT:返回受上一语句影响的行数。
返回类型:integer。
注释:任何不返回行的语句将这一变量设置为 0 ,如 IF 语句。
示例:下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
print 'Warning: No rows were updated'结果:(所影响的行数为 0 行)
Warning: No rows were updated
/*
标题:SQL SERVER 2005中查询指定节点及其所有父节点的方法(表格形式显示)
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2010-02-02
地点:新疆乌鲁木齐
*/create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))
insert into tb values('001' , null , N'广东省')
insert into tb values('002' , '001' , N'广州市')
insert into tb values('003' , '001' , N'深圳市')
insert into tb values('004' , '002' , N'天河区')
insert into tb values('005' , '003' , N'罗湖区')
insert into tb values('006' , '003' , N'福田区')
insert into tb values('007' , '003' , N'宝安区')
insert into tb values('008' , '007' , N'西乡镇')
insert into tb values('009' , '007' , N'龙华镇')
insert into tb values('010' , '007' , N'松岗镇')
goDECLARE @ID VARCHAR(3)--查询ID = '001'的所有父节点
SET @ID = '001'
;WITH T AS
(
SELECT ID , PID , NAME
FROM TB
WHERE ID = @ID
UNION ALL
SELECT A.ID , A.PID , A.NAME
FROM TB AS A JOIN T AS B ON A.ID = B.PID
)
SELECT * FROM T ORDER BY ID
/*
ID PID NAME
---- ---- ----------
001 NULL 广东省(1 行受影响)
*/--查询ID = '002'的所有父节点
SET @ID = '002'
;WITH T AS
(
SELECT ID , PID , NAME
FROM TB
WHERE ID = @ID
UNION ALL
SELECT A.ID , A.PID , A.NAME
FROM TB AS A JOIN T AS B ON A.ID = B.PID
)
SELECT * FROM T ORDER BY ID
/*
ID PID NAME
---- ---- ----------
001 NULL 广东省
002 001 广州市(2 行受影响)
*/--查询ID = '003'的所有父节点
SET @ID = '003'
;WITH T AS
(
SELECT ID , PID , NAME
FROM TB
WHERE ID = @ID
UNION ALL
SELECT A.ID , A.PID , A.NAME
FROM TB AS A JOIN T AS B ON A.ID = B.PID
)
SELECT * FROM T ORDER BY ID
/*
ID PID NAME
---- ---- ----------
001 NULL 广东省
003 001 深圳市(2 行受影响)
*/--查询ID = '009'的所有父节点
SET @ID = '009'
;WITH T AS
(
SELECT ID , PID , NAME
FROM TB
WHERE ID = @ID
UNION ALL
SELECT A.ID , A.PID , A.NAME
FROM TB AS A JOIN T AS B ON A.ID = B.PID
)
SELECT * FROM T ORDER BY ID
/*
ID PID NAME
---- ---- ----------
001 NULL 广东省
003 001 深圳市
007 003 宝安区
009 007 龙华镇(4 行受影响)
*/drop table tb--注:除ID值不一样外,四个SQL语句是一样的。
把上面的做成子查询,然后select count(1) from (...) t
create table #t (id int,name varchar(1),pid int)
insert into #t
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'d',2with cte as
(
select * from #t where id=4
union all
select #t.* from cte
join #t on cte.pid=#t.id
)
--select * from cte
select count(*) from cte where id<>4
-----------
2(1 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[pid] int)
insert [tb]
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',1 union all
select 4,'d',2--------------------------------查询开始------------------------------DECLARE @s VARCHAR(10)
SET @s='c'
if object_id('tempdb..#') is not null
drop table #;
SELECT id, pid,[name] INTO # FROM tb WHERE [name]=@s
WHILE @@ROWCOUNT>0
BEGIN
INSERT INTO # SELECT t.id, t.pid,t.[name] FROM tb AS t
INNER JOIN # AS a ON t.id=a.pid AND t.id NOT IN(SELECT ID FROM #)
END
SELECT count(*) FROM #
/*
-----------
2(1 行受影响)
*/