--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (BusinessID int,Level int,SponsorID int)
insert into #T
select 27111618,1,27111608 union all
select 27111617,2,27111618 union all
select 27111675,2,27111618 union all
select 27111676,3,27111675 union all
select 27111527,4,27111676 union all
select 27111682,4,27111676 union all
select 27111692,4,27111676 union all
select 27111723,4,27111676 union all
select 27123212,4,27111676 union all
select 27123228,4,27111676 union all
select 27123240,4,27111676 union all
select 27111454,5,27123240 union all
select 27111457,5,27123240 union all
select 27123254,5,27123240 union all
select 27111683,3,27111675 union all
select 27111468,4,27111683 union all
select 27111530,4,27111683 union all
select 27111664,4,27111683 union all
select 27123236,2,27111618;with T as
(
select BusinessID,Level=1,SponsorID from #T where BusinessID=27111676
union all
select a.BusinessID,b.Level+1,a.SponsorID from #T a join T b on a.SponsorID=b.BusinessID
)
select * from T order by 2,1
/*
BusinessID Level SponsorID
----------- ----------- -----------
27111676 1 27111675
27111527 2 27111676
27111682 2 27111676
27111692 2 27111676
27111723 2 27111676
27123212 2 27111676
27123228 2 27111676
27123240 2 27111676
27111454 3 27123240
27111457 3 27123240
27123254 3 27123240
*/
还有2楼的能用你的方法结合我的实际情况写一段吗 我从来没写过这么复杂的sql 请各位指教 明天分数加到50分 如果不够我再加
create table T (BusinessID int,Level int,SponsorID int)
insert into T
select 27111618,1,27111608 union all
select 27111617,2,27111618 union all
select 27111675,2,27111618 union all
select 27111676,3,27111675 union all
select 27111527,4,27111676 union all
select 27111682,4,27111676 union all
select 27111692,4,27111676 union all
select 27111723,4,27111676 union all
select 27123212,4,27111676 union all
select 27123228,4,27111676 union all
select 27123240,4,27111676 union all
select 27111454,5,27123240 union all
select 27111457,5,27123240 union all
select 27123254,5,27123240 union all
select 27111683,3,27111675 union all
select 27111468,4,27111683 union all
select 27111530,4,27111683 union all
select 27111664,4,27111683 union all
select 27123236,2,27111618--创建函数查询指定节点及其所有子节点的函数
create FUNCTION get_cid(@BusinessID varchar(10))
RETURNS @t TABLE(BusinessID varchar(10),Level int,SponsorID varchar(10))
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t SELECT businessid,@level,SponsorID from t where businessid=@BusinessID
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.BusinessID,@Level,a.SponsorID FROM t a,@t b
WHERE a.SponsorID=b.BusinessID AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询27111675以及其所有子节点:
select * from dbo.get_cid('27111675')