求一级联查询Sql语句
我有一个表保存Id,PId,Name三个字段,分别表示编号,父编号和名称,我要实现的是给定一个编号,查出该编号下的所有后代.
如:
Id PId Name
1 0 a
2 0 b
3 1 c
4 3 d
5 4 e
6 4 f
如果我指定查询Id=1,结果要是:3,4,5,6
如果Id=2,结果为空
如果Id=3,结果要为:4,5,6
.....
[解决办法]
/*标题：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_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)asbegin declare @level int set @level = 1 insert into @t_level select @id , @level while @@ROWCOUNT > 0 begin set @level = @level + 1 insert into @t_level select a.id , @level from tb a , @t_Level b where a.pid = b.id and b.level = @level - 1 end returnendgo--调用函数查询001(广东省)及其所有子节点select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id/*id pid name ---- ---- ---------- 001 NULL 广东省002 001 广州市003 001 深圳市004 002 天河区005 003 罗湖区006 003 福田区007 003 宝安区008 007 西乡镇009 007 龙华镇010 007 松岗镇（所影响的行数为 10 行）*/--调用函数查询002(广州市)及其所有子节点select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id/*id pid name ---- ---- ---------- 002 001 广州市004 002 天河区（所影响的行数为 2 行）*/--调用函数查询003(深圳市)及其所有子节点select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id/*id pid name ---- ---- ---------- 003 001 深圳市005 003 罗湖区006 003 福田区007 003 宝安区008 007 西乡镇009 007 龙华镇010 007 松岗镇（所影响的行数为 7 行）*/drop table tbdrop function f_cid
[解决办法]
/*标题：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.PID = B.ID)SELECT * FROM T ORDER BY ID/*ID PID NAME---- ---- ----------001 NULL 广东省002 001 广州市003 001 深圳市004 002 天河区005 003 罗湖区006 003 福田区007 003 宝安区008 007 西乡镇009 007 龙华镇010 007 松岗镇(10 行受影响)*/--查询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.PID = B.ID)SELECT * FROM T ORDER BY ID/*ID PID NAME---- ---- ----------002 001 广州市004 002 天河区(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.PID = B.ID)SELECT * FROM T ORDER BY ID/*ID PID NAME---- ---- ----------003 001 深圳市005 003 罗湖区006 003 福田区007 003 宝安区008 007 西乡镇009 007 龙华镇010 007 松岗镇(7 行受影响)*/drop table tb--注：除ID值不一样外，三个SQL语句是一样的。
[解决办法]
---------------------------------------- Author : htl258(Tony)-- Date : 2010-04-30 13:36:20-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Blog : http://blog.csdn.net/htl258----------------------------------------> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([Id] INT,[PId] INT,[Name] NVARCHAR(10))INSERT [tb]SELECT 1,0,'a' UNION ALLSELECT 2,0,'b' UNION ALLSELECT 3,1,'c' UNION ALLSELECT 4,3,'d' UNION ALLSELECT 5,4,'e' UNION ALLSELECT 6,4,'f'GO--SELECT * FROM [tb]-->SQL查询如下:IF NOT OBJECT_ID('[p_gettree]') IS NULL DROP PROC [p_gettree]GOCREATE PROC p_gettree @id intASWITH T AS ( SELECT * FROM tb WHERE ID=@ID UNION ALL SELECT A.* FROM tb A JOIN T B ON A.PId=B.Id)SELECT ID FROM T WHERE ID<>@IDGOEXEC [p_gettree] 1/*ID-----------3456(4 行受影响)*/EXEC [p_gettree] 2/*ID-----------(0 行受影响)*/EXEC [p_gettree] 3/*ID-----------456(3 行受影响)*/
[解决办法]
--写个2000的吧------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2010-04-30 14:49:16-- Version:-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([Id] int,[PId] int,[Name] varchar(1))insert [tb]select 1,0,'a' union allselect 2,0,'b' union allselect 3,1,'c' union allselect 4,3,'d' union allselect 5,4,'e' union allselect 6,4,'f'--------------开始查询--------------------------if object_id('Uf_GetChildID')is not null drop function Uf_GetChildIDgocreate function Uf_GetChildID(@pid int)returns @t table(ID int)asbegin insert @t select ID from tb where pid=@pid while @@rowcount<>0 begin insert @t select a.ID from tb a inner join @t b on a.pid=b.id and not exists(select 1 from @t where id=a.id) end returnendgoselect * from dbo.Uf_GetChildID(1)----------------结果----------------------------/* ID-----------3456(4 行受影响)*/