set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[getMID](@MID int)
returns varchar(500)
as
begin
declare @MIDS varchar(200)
set @MIDS=''
;with cte as
(
select MID,MPID from Member_Tbl where MID=@MID
union all
select m.MID,m.MPID from cte inner join Member_Tbl m on cte.MID=m.MPID
)
select @MIDS=@MIDS+','+convert(varchar(100),MID) from cte
set @MIDS=stuff(@MIDS,1,1,'')
return @MIDS
end--执行--
select * from Member_Tbl where charindex(','+convert(varchar(200),MID)+',',','+dbo.getMID(1)+',')>0sql函数的执行效率怎么样呢？
那我上面那个语句该怎么改呢？
Member_Tbl这个表的数据后期还蛮多的！
对于sql 自己只能做到这步了，该怎么改呢？
declare @t table (MID int,MPID int)
insert into @t
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,0 union all
select 5,0DECLARE @i INT;SET @i=1 --参数自己改
;WITH maco AS
(select * from @t WHERE mid=@i UNION ALL
SELECT a.* FROM @t a,maco WHERE a.MPID=maco.MID)SELECT * FROM maco
/*
MID MPID
----------- -----------
1 0
2 1
3 2
*/直接得到下级列表
可是要用在存储过程分页里，取得MID当作参数处理的，
;with xx as没地方加额
不知道我说的明白不？
(
@pageindex INT, --1
@pagesize INT,--2
@MID int
)
AS
begin
declare @t table (MID int,MPID int)
insert into @t
select 1,0 union all
select 2,1 union all
select 3,2 union all
select 4,0 union all
select 5,0 DECLARE @i INT;SET @i=1 --参数自己改
;WITH maco AS
(select * from @t WHERE mid=@i UNION ALL
SELECT a.* FROM @t a,maco WHERE a.MPID=maco.MID) SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY mid) AS rowid, * FROM maco
) bb
WHERE rowid
BETWEEN (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
ENDEXEC procname 1,2,1
/*
rowid MID MPID
-------------------- ----------- -----------
1 1 0
2 2 1
*/
EXEC procname 2,2,1
/*
rowid MID MPID
-------------------- ----------- -----------
3 3 2
*/
string tablename = "Orders_Tbl o inner join Products_Tbl p on o.ID=p.ID"这样