CREATE TABLE [t_user] (
[t_id] [int] IDENTITY (1, 1) NOT NULL ,
[t_ownerid] [int] NULL ,
CONSTRAINT [PK_t_user] PRIMARY KEY CLUSTERED
(
[t_id]
) ON [PRIMARY]
) ON [PRIMARY]
--第一种方法
print convert(varchar,getdate(),120)
with cte as
(
select t_id,t_ownerid from t_users where t_ownerid=56
union all
select b.t_id,b.t_ownerid from cte as a,t_users as b where a.t_id=b.t_ownerid
)
select * from cte
print convert(varchar,getdate(),120)
--第二种方法
declare id_cursor cursor local for select t_id,t_ownerid from t_users
declare @t_id int,@t_ownerid int,@str varchar(50),@m_id int
set @m_id=56
set @str=cast(@m_id as varchar)
--打开游标
Open id_cursor
--循环并提取记录
Fetch Next From id_cursor Into @t_id,@t_ownerid--取第一条记录存入@result中
While ( @@Fetch_Status=0 )
begin
if @m_id<>@t_id
begin
declare @pos int
select @pos=charindex(cast(@t_ownerid as varchar),@str)
if @pos>0
begin
declare @mstr varchar(50),@state bit
set @state=1
if @pos>1
begin
select @mstr=substring(@str,@pos-1,1)
if (@mstr not like '%[^0-9]%'
and @mstr like '[1-9]%')
set @state=0
end
if @state=1
begin
if @pos<len(@str)
begin
select @mstr=substring(@str,@pos+len(@t_ownerid),1)
if (@mstr not like '%[^0-9]%'
and @mstr like '[1-9]%')
set @state=0
end
end
if @state=1
set @str=@str+','+cast(@t_id as varchar)
else
print @mstr
end
end
Fetch Next From id_cursor Into @t_id,@t_ownerid
end
--关闭游标
Close id_cursor
--释放游标
Deallocate id_cursor
select [归属id]=@str
print convert(varchar,getdate(),120)