它最终查询出来 数据库里某一个userid下面关联的所有用户 怎么查询的,而且查询很慢,能不能快一些create PROCEDURE [dbo].[test]
@userId varchar(12),
@varUserIdList varchar(max) output
AS
declare @rowCount int
declare @i int
declare @varUserId varchar(max)
declare @varRelationId varchar(50)
declare @CurrentIndex int
declare @NextIndex int
declare @varSubUserId varchar(12)
declare @varSubSubUserId varchar(15)
BEGIN
set @CurrentIndex=1;
set @rowCount =0
set @i=1
set @varUserId=@userId+'',''
set @varSubSubUserId=@varUserId
set @varUserIdList=''null''
while(@CurrentIndex<=datalength(@varUserId))
begin
SELECT @NextIndex=charindex('','',@varUserId,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=datalength(@varUserId)+1;--DATALENGTH(@SplitString)/2
SELECT @varSubUserId=substring(@varUserId,@CurrentIndex,@NextIndex-@CurrentIndex);
SELECT @CurrentIndex=@NextIndex+1;
select @rowCount=count(*) from userRelation where placementId=@varSubUserId
set @i=1
if(@rowCount>0)
begin
while(@i<=@rowCount)
begin
if(@i=1)
begin
select top 1 @userId=userId ,@varRelationId=userId from userRelation where placementID=@varSubUserId order by userId asc
set @varUserId=@varUserId+@userId+'',''
end
else
begin
select top 1 @userId=userId ,@varRelationId=userId from userRelation where placementID=@varSubUserId and userId>@varRelationId order by userId asc
set @varUserId=@varUserId+@userId+'',''
end
set @i=@i+1
end
end
end
set @varUserIdList = @varUserId
set @varUserIdList=replace(@varUserIdList,@varSubSubUserId,'''')END
@userId varchar(12),
@varUserIdList varchar(max) output
AS
declare @rowCount int
declare @i int
declare @varUserId varchar(max)
declare @varRelationId varchar(50)
declare @CurrentIndex int
declare @NextIndex int
declare @varSubUserId varchar(12)
declare @varSubSubUserId varchar(15)
BEGIN
set @CurrentIndex=1;
set @rowCount =0
set @i=1
set @varUserId=@userId+'',''
set @varSubSubUserId=@varUserId
set @varUserIdList=''null''
while(@CurrentIndex<=datalength(@varUserId))
begin
SELECT @NextIndex=charindex('','',@varUserId,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=datalength(@varUserId)+1;--DATALENGTH(@SplitString)/2
SELECT @varSubUserId=substring(@varUserId,@CurrentIndex,@NextIndex-@CurrentIndex);
SELECT @CurrentIndex=@NextIndex+1;
select @rowCount=count(*) from userRelation where placementId=@varSubUserId
set @i=1
if(@rowCount>0)
begin
while(@i<=@rowCount)
begin
if(@i=1)
begin
select top 1 @userId=userId ,@varRelationId=userId from userRelation where placementID=@varSubUserId order by userId asc
set @varUserId=@varUserId+@userId+'',''
end
else
begin
select top 1 @userId=userId ,@varRelationId=userId from userRelation where placementID=@varSubUserId and userId>@varRelationId order by userId asc
set @varUserId=@varUserId+@userId+'',''
end
set @i=@i+1
end
end
end
set @varUserIdList = @varUserId
set @varUserIdList=replace(@varUserIdList,@varSubSubUserId,'''')END
set @varUserIdList=''null'' 这种好像没有见过
是mssql 表结构如下
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[userRelation]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[userRelation](
[userid] [varchar](20) NOT NULL,
[sponsorid] [varchar](20) NULL,
[placementid] [varchar](20) NULL,
[direct] [nchar](10) NULL,
CONSTRAINT [PK_USERRELATION] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
把所有的 '''' 换成 ''
set @varUserIdList=''null''
改为
set @varUserIdList=''
--没看明白你到底要做啥!create PROCEDURE [dbo].[test]
@userId varchar(12),
@varUserIdList varchar(max) output
AS
declare @rowCount int
declare @i int
declare @varUserId varchar(max)
declare @varRelationId varchar(50)
declare @CurrentIndex int
declare @NextIndex int
declare @varSubUserId varchar(12)
declare @varSubSubUserId varchar(15)
BEGIN
set @CurrentIndex=1;
set @rowCount =0
set @i=1
set @varUserId=@userId+','
set @varSubSubUserId=@varUserId
set @varUserIdList=''
while(@CurrentIndex<=datalength(@varUserId))
begin
SET @NextIndex=charindex(',',@varUserId,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SET @NextIndex=datalength(@varUserId)+1;--DATALENGTH(@SplitString)/2
SET @varSubUserId=substring(@varUserId,@CurrentIndex,@NextIndex-@CurrentIndex);
SET @CurrentIndex=@NextIndex+1;
select @rowCount=count(*) from userRelation where placementId=@varSubUserId
set @i=1
if(@rowCount>0)
begin
while(@i<=@rowCount)
begin
if(@i=1)
begin
select top 1 @userId=userId ,@varRelationId=userId from userRelation where placementID=@varSubUserId order by userId asc
set @varUserId=@varUserId+@userId+','
end
else
begin
select top 1 @userId=userId ,@varRelationId=userId from userRelation where placementID=@varSubUserId and userId>@varRelationId order by userId asc
set @varUserId=@varUserId+@userId+','
end
set @i=@i+1
end
end
end
set @varUserIdList = @varUserId
set @varUserIdList=replace(@varUserIdList,@varSubSubUserId,'')END--大小写最好一致。