有一张用户表，里面的用户有推荐关系！
我现在新增了几个新用户！
我想往推荐人身上加积分！
假如 A→B→C→D→E→F→G→H
→表示推荐
我现在获取到了H H用户有一个字段是他的推荐人ID
我如何往G上加完推荐积分，
然后在查G的推荐人F，往F头上加积分之后
又查到F的推荐人E。这样一直循环加到A
用游标可以实现嘛 ！？？
------解决方案--------------------
- SQL code
if object_id('[tb]') is not null drop table [tb]create table [tb] (id int,name varchar(1),pid int)insert into [tb]select 1,'A',0 union allselect 2,'B',1 union allselect 3,'D',1 union allselect 4,'C',2 union allselect 5,'D',2 union allselect 6,'A',4 union allselect 7,'E',5 union allselect 8,'F',5GO;with cteas( select *,[path]=cast([name]+'->' as varchar(100)) ,[level] = 1 from tb where pid = 0 union all select a.*, cast(c.[path]+a.[name]+'->' as varchar(100)),[level]+1 from cte c ,tb a where a.pid = c.id)select * from ctewhere len([path]) > 6 and right([path],3) = left([path],3)/*id name pid path level----------- ---- ----------- -------------- -----6 A 4 A->B->C->A-> 4(1 行受影响)*/-------------------------------------- Author : happyflystone -- Date : 2010-04-06 -- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) -- Apr 14 2006 01:12:25 -- Copyright (c) 1988-2005 Microsoft Corporation-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)-- -------------------------------------- Test Data: taIF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GoCREATE TABLE tb([cid] NVARCHAR(1),[pid] NVARCHAR(1))GoINSERT INTO tb SELECT 'A','B' UNION ALL SELECT 'A','D' UNION ALL SELECT 'B','C' UNION ALL SELECT 'B','D' UNION ALL SELECT 'C','A' UNION ALL SELECT 'D','E' UNION ALL SELECT 'D','F' GO--Start;with cteas( select *,[path]=cast([cid]+'->' as varchar(100)) ,[level] = 1 from (select distinct cid,cast('' as nvarchar(1)) as pid from tb union select distinct pid ,'' from tb) b union all select a.*,cast(a.[cid]+'->'+c.[path] as varchar(100)),[level]+1 from cte c ,tb a where a.pid = c.cid and charindex(a.[cid]+'->',c.[path])=0)select [path]+cid+'->'from ctewhere exists(select 2 from tb where cid+'->' = right([path],3) and pid+'->' = left([path],3))-- = left([path],3)--Result:/*--------------A->B->C->A->C->A->B->C->B->C->A->B->(3 行受影响)*/--End 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/06/5456223.aspxBOM循环问题，参考上诉资料
------解决方案--------------------
参考下面的内容,自己修改一下即可.
- SQL code
/*标题：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))asbegin 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 returnendgo--调用函数查询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 tbdrop function f_pid