34,576
社区成员
发帖
与我相关
我的任务
分享
--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@split varchar(10), --分隔字符
@s varchar(8000) --要分拆的字符串
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
任务id int,
奖励 varchar(1000)
)
insert into tb
select 1,'1:10,2:20,3:25' union all
select 2,'6:16,7:23,8:28'
declare @t table(id int identity(1,1),a int, b int)
declare @i int
declare @count int
insert into @t
select left(t.col,charindex(':',col)-1),
SUBSTRING(t.col,charindex(':',col)+1,LEN(t.col))
from tb
cross apply dbo.f_splitSTR(',',tb.奖励)t
where 任务id = 2
set @i =1
set @count = (select COUNT(*) from @t)
--循环
while @i <= @count
begin
select a,b from @t where id = @i
set @i = @i + 1
end
[/quote]
所以里面的from tb不需要,后面的任务id=2也不需要,怎么改呢?[/quote]
我加你关注了,可以发私信。
--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@split varchar(10), --分隔字符
@s varchar(8000) --要分拆的字符串
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
任务id int,
奖励 varchar(1000)
)
insert into tb
select 1,'1:10,2:20,3:25' union all
select 2,'6:16,7:23,8:28'
declare @t table(id int identity(1,1),a int, b int)
declare @i int
declare @count int
insert into @t
select left(t.col,charindex(':',col)-1),
SUBSTRING(t.col,charindex(':',col)+1,LEN(t.col))
from tb
cross apply dbo.f_splitSTR(',',tb.奖励)t
where 任务id = 2
set @i =1
set @count = (select COUNT(*) from @t)
--循环
while @i <= @count
begin
select a,b from @t where id = @i
set @i = @i + 1
end
[/quote]
所以里面的from tb不需要,后面的任务id=2也不需要,怎么改呢?
--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@split varchar(10), --分隔字符
@s varchar(8000) --要分拆的字符串
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
任务id int,
奖励 varchar(1000)
)
insert into tb
select 1,'1:10,2:20,3:25' union all
select 2,'6:16,7:23,8:28'
declare @t table(id int identity(1,1),a int, b int)
declare @i int
declare @count int
insert into @t
select left(t.col,charindex(':',col)-1),
SUBSTRING(t.col,charindex(':',col)+1,LEN(t.col))
from tb
cross apply dbo.f_splitSTR(',',tb.奖励)t
where 任务id = 2
set @i =1
set @count = (select COUNT(*) from @t)
--循环
while @i <= @count
begin
select a,b from @t where id = @i
set @i = @i + 1
end
[/quote]
没有这么复杂。。
表设计是:
任务id 任务奖励
商品奖励其实去在存储过程的最前面已经获取到了,
declare @jiangli nvarchar(50)
select @jiangli=col(列明) where 任务id=2
这个时候已经存在了这一行奖励,只不过这个奖励通过函数f_split获取到一个表格,我们是要循环这个表格的。
--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@split varchar(10), --分隔字符
@s varchar(8000) --要分拆的字符串
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
任务id int,
奖励 varchar(1000)
)
insert into tb
select 1,'1:10,2:20,3:25' union all
select 2,'6:16,7:23,8:28'
select *
from tb
cross apply dbo.f_splitSTR(',',tb.奖励)t
where 任务id = 2
/*
任务id 奖励 col
2 6:16,7:23,8:28 6:16
2 6:16,7:23,8:28 7:23
2 6:16,7:23,8:28 8:28
*/
--1.函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@split varchar(10), --分隔字符
@s varchar(8000) --要分拆的字符串
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb
(
任务id int,
奖励 varchar(1000)
)
insert into tb
select 1,'1:10,2:20,3:25' union all
select 2,'6:16,7:23,8:28'
declare @t table(id int identity(1,1),a int, b int)
declare @i int
declare @count int
insert into @t
select left(t.col,charindex(':',col)-1),
SUBSTRING(t.col,charindex(':',col)+1,LEN(t.col))
from tb
cross apply dbo.f_splitSTR(',',tb.奖励)t
where 任务id = 2
set @i =1
set @count = (select COUNT(*) from @t)
--循环
while @i <= @count
begin
select a,b from @t where id = @i
set @i = @i + 1
end