数据库表有
id typeids key
1 1,3,5 234234
2 2,5 4634534
3 3,5,6 2345235
4 2,3,5 12341234能否写一个存储过程
ALTER PROCEDURE [dbo].[p_RadioList_SelectAudioByPage]
(
@key
@序号
@返回key对应的typeids 总个数
)
当key是234234 序号等于 1 的时候 取到的是 typeids 的 1 返回的是个数 3
当key是234234 序号等于 2 的时候 取到的是 typeids 的 3 返回的是个数 3
当key是12341234 序号等于 3 的时候 取到的是 typeids 的 5 返回的个数是3
以此类推,就是按逗号把typeids坼分,并按序号取到对应的某一个
id typeids key
1 1,3,5 234234
2 2,5 4634534
3 3,5,6 2345235
4 2,3,5 12341234能否写一个存储过程
ALTER PROCEDURE [dbo].[p_RadioList_SelectAudioByPage]
(
@key
@序号
@返回key对应的typeids 总个数
)
当key是234234 序号等于 1 的时候 取到的是 typeids 的 1 返回的是个数 3
当key是234234 序号等于 2 的时候 取到的是 typeids 的 3 返回的是个数 3
当key是12341234 序号等于 3 的时候 取到的是 typeids 的 5 返回的个数是3
以此类推,就是按逗号把typeids坼分,并按序号取到对应的某一个
不懂为啥
不过要求个数,SELECT LEN(typeids )-LEN(REPLACE(typeids ,',','')+1
select len(typeids)-len(replace(typeids,',','')
Alter PROCEDURE [dbo].[p_RadioList_SelectAudioByPage]
(
@key int,
@seq int
)
AS
declare @typeids varchar(20), @cnt int,@n int
set @n=1
select @typeids=typeids,@cnt=len(typeids)-len(replace(typeids,',',''))+1
from T
where [key]=@key
if @typeids is null
select N'输入的key无效'
else if @seq>@cnt
select N'输入的seq超出范围'
else
begin
while @n<@seq
begin
select @typeids=stuff(@typeids,1,charindex(',',@typeids),'')
set @n=@n+1
end
select substring(@typeids,1,case when charindex(',',@typeids)>0 then charindex(',',@typeids)-1 else len(@typeids) end)
end
GO
Alter PROCEDURE [dbo].[p_RadioList_SelectAudioByPage]
(
@key int,
@seq int
)
AS
declare @typeids varchar(20), @cnt int,@n int
set @n=1
select @typeids=typeids,@cnt=len(typeids)-len(replace(typeids,',',''))+1
from T
where [key]=@key
if @typeids is null
select N'输入的key无效'
else if @seq>@cnt
select N'输入的seq超出范围'
else
begin
while @n<@seq
begin
select @typeids=stuff(@typeids,1,charindex(',',@typeids),'')
set @n=@n+1
end
select substring(@typeids,1,case when charindex(',',@typeids)>0 then charindex(',',@typeids)-1 else len(@typeids) end) as [typeids],@cnt as cnt
end
GO
if exists (select 1 from sysobjects where OBJECT_ID('tab2') is not null and [type]='U' )
drop table tab2
Create table tab2 (id int,typeids varchar(50),[key] int)
insert into tab2 select 1,'1,3,5',234234 union all
select 2,'2,5',234234 union all
select 3,'3,5,6',234234 union all
select 4,'2,3,5',234234
Go
Alter proc p_RadioList_SelectAudioByPage
(
@key int,
@number int
)
as
begin
declare @typeids int,@numall int
set @typeids=0
set @numall=0
SELECT @numall = LEN(typeids )-LEN(REPLACE(typeids ,',',''))+1 from tab2 where [key] =@key
select @typeids= substring(REPLACE(typeids,',',''),@number,1) from tab2 where [key] =@key
select @typeids as 'typeids',@numall as '总数'
end
if exists (select 1 from sysobjects where OBJECT_ID('tab2') is not null and [type]='U' )
drop table tab2
Create table tab2 (id int,typeids varchar(50),[key] int)
insert into tab2 select 1,'1,3,5',234234 union all
select 2,'2,5',4634534 union all
select 3,'3,5,6',2345235 union all
select 4,'2,3,5',12341234 union all
select 5,'2,3,5,56',1234512345
Go-- Key 是关键字
--EXEC sp_rename 'tab2.key', 'kye', 'COLUMN';DROP PROCEDURE p_RadioList_SelectAudioByPage
GO
CREATE PROCEDURE p_RadioList_SelectAudioByPage
(@key int,
@number int
)
ASSET NOCOUNT ONDECLARE @TypeIDList table(TypeID nvarchar(30),id int IDENTITY(1,1))
DECLARE @iCount_TypeID int
DECLARE @ilength_TypeID int
DECLARE @TypeID nvarchar(30)
DECLARE @TypeIDs nvarchar(max)IF NOT EXISTS (SELECT 1 FROM tab2 WHERE tab2.kye = @key)
BEGIN
PRINT 'Key Not Existed'
RETURN
END
ELSE
BEGIN
SELECT @TypeIDs = TypeIDs FROM tab2 WHERE tab2.kye = @key
SET @iCount_TypeID = 1
WHILE @iCount_TypeID <> 0
BEGIN
SET @iCount_TypeID = PATINDEX('%,%',@TypeIDs)
SET @ilength_TypeID = CASE @iCount_TypeID WHEN 0 THEN DATALENGTH(@TypeIDs) ELSE @iCount_TypeID-1 END
SET @TypeID = SUBSTRING(@TypeIDs,1,@ilength_TypeID)
SET @TypeIDs = SUBSTRING(@TypeIDs,@iCount_TypeID+1,DATALENGTH(@TypeIDs))
BEGIN
INSERT INTO @TypeIDList
VALUES(@TypeID)
END
END
ENDDECLARE @MAx int
SELECT @Max = MAX(ID) FROM @TypeIDListIF @Max < @number
BEGIN
PRINT 'Rank Over'
RETURN
END
ELSE
BEGIN
SELECT TypeID,ID,Count_Comma = @MAx - 1
FROM @TypeIDList WHERE id = @number
GROUP BY TypeID,ID
ENDSET NOCOUNT OFF-- 测试用例p_RadioList_SelectAudioByPage
@key = '1234512345',@number = 5--SELECT * FROM tab2
insert into test select 1 ,'1,3,5', '234234'
insert into test select 2 , '2,5' , '4634534'
insert into test select 3 , '3,5,6' , '2345235'
insert into test select 4 , '2,3,5' , '12341234' go
create proc linproc(@key varchar(100),@id int,@count int output)
as
beginselect @count=len(typeids)-len(replace(typeids,',','')) from test where id=@id and key1=@key
return
end
go
declare @count int
exec linproc '234234',1,@count output
select @count
go
drop table test
drop proc linproc