有一个字符串,
s = '01-100,02-100,03-100,04-100,05-100,06-100,07-100,08-100,09-100,10-100,11-100,12-100,13-100,14-100,15-100,16-100,17-100,18-100,19-100,20-100,21-100,22-100,23-100,00-100,'"-"前代表的是时间,01点,02点...00点,'-'后面代表的是这个点对应的一个数量值,在存储过程中,如何根据程序传来的变量,获得对应的数量值?(传来的变量值就是前面说的点)
s = '01-100,02-100,03-100,04-100,05-100,06-100,07-100,08-100,09-100,10-100,11-100,12-100,13-100,14-100,15-100,16-100,17-100,18-100,19-100,20-100,21-100,22-100,23-100,00-100,'"-"前代表的是时间,01点,02点...00点,'-'后面代表的是这个点对应的一个数量值,在存储过程中,如何根据程序传来的变量,获得对应的数量值?(传来的变量值就是前面说的点)
declare @s varchar(2000)
declare @t table (hh int,num int)
set @s = '01-100,02-100,03-100,04-100,05-100,06-100,07-100,08-100,
09-100,10-100,11-100,12-100,13-100,14-100,15-100,16-100,
17-100,18-100,19-100,20-100,21-100,22-100,23-100,00-100,'set @s = 'select '+replace(replace(@s,',',' as num union all select '),'-',' as hh,')
set @s = left(@s,len(@s)-17)
insert into @t exec(@s)
select * from @t -- where hh = 9/*******************hh num
----------- -----------
1 100
2 100
3 100
4 100
5 100
6 100
7 100
8 100
9 100
10 100
11 100
12 100
13 100
14 100
15 100
16 100
17 100
18 100
19 100
20 100
21 100
22 100
23 100
0 100(24 行受影响)
DECLARE @Month VARCHAR(10) = '09' --参数
DECLARE @Return VARCHAR(100)SELECT @Return = SUBSTRING(@S,CHARINDEX(@Month + '-',@s) + 3,3)
declare @str varchar(100)
set @str='
01-100,02-100,03-100,04-100,05-100,06-100,07-100,08-100,09-100,10-100,11-100,12-100,13-100,14-100,15-100,16-100,17-100,18-100,19-100,20-100,21-100,22-100,23-100,00-100
'
declare @month int
set @month=9
select substring(@str,
CHARINDEX(','+right('00'+LTRIM(@month),2)+'-',','+@str)+3,
CHARINDEX(','+right('00'+LTRIM(@month+1),2)+'-',','+@str)-
CHARINDEX(','+right('00'+LTRIM(@month),2)+'-',','+@str)-4)
as value
/*
value
100
*/--如果你的value都是3位数,可以用楼上的,不是的话我这个可以
declare @str varchar(100)
set @str='
01-100,02-100,03-100,04-100,05-100,06-100,07-100,08-100,09-100,10-100,11-100,12-100,13-100,14-100,15-100,16-100,17-100,18-100,19-100,20-100,21-100,22-100,23-100,00-100
'
declare @month int
set @month=9
select substring(@str,
CHARINDEX(','+right('00'+LTRIM(@month),2)+'-',','+@str)+3,
CHARINDEX(',',','+@str,CHARINDEX(','+right('00'+LTRIM(@month+1),2)+'-',','+@str))-
CHARINDEX(','+right('00'+LTRIM(@month),2)+'-',','+@str)-4)
as value
想想循环,可是感觉好慢啊declare @s nvarchar(50)
declare @i int
declare @sl int
set @s='a,b,c,d'
set @i=charindex(',',@s)
set @sl=len(@s)while @i>=1
begin
print substring(@s,@i-1,1)
set @s=substring(@s,@i+1,len(@s)-@i)
set @i=charindex(',',@s)
end
print @s
if OBJECT_ID('pro_test','p')is not null
drop proc pro_test
go
create proc pro_test
(@month int,@str varchar(100))
as
declare @a int
declare @b int
select @a=CHARINDEX(','+right('00'+LTRIM(@month),2),','+@str)+3
select @b=CHARINDEX(',',@str,@a)
select SUBSTRING(@str,@a,@b-@a) as value
go
exec pro_test 7,'
01-100,02-100,03-100,04-1,05-10,06-100,07-10000,
08-100,09-100,10-100,11-100,12-100,13-100,
14-100,15-100,16-100,17-100,18-100,19-100,
20-100,21-100,22-100,23-100,00-100,
'
/*
value
10000
*/
exec pro_test 4,'
01-100,02-100,03-100,04-1,05-10,06-100,07-10000,
08-100,09-100,10-100,11-100,12-100,13-100,
14-100,15-100,16-100,17-100,18-100,19-100,
20-100,21-100,22-100,23-100,00-100,'
/*
value
1
*/exec pro_test 5,'
01-100,02-100,03-100,04-1,05-10,06-100,07-10000,
08-100,09-100,10-100,11-100,12-100,13-100,
14-100,15-100,16-100,17-100,18-100,19-100,
20-100,21-100,22-100,23-100,00-100,'
/*
value
10
*/
exec pro_test 9,'
01-100,02-100,03-100,04-1,05-10,06-100,07-10000,
08-100,09-100,10-100,11-100,12-100,13-100,
14-100,15-100,16-100,17-100,18-100,19-100,
20-100,21-100,22-100,23-100,00-100,'
/*
value
100
*/
不知道你还会出什么问题
declare @s varchar(2000)
declare @t table (hh int,num int)
set @s = '01-100,02-100,03-100,04-100,05-100,06-100,07-100,08-100,
09-100,10-100,11-100,12-100,13-100,14-100,15-100,16-100,
17-100,18-100,19-100,20-100,21-100,22-100,23-100,00-100,'set @s = 'select '+replace(replace(@s,',',' as num union all select '),'-',' as hh,')
set @s = left(@s,len(@s)-17)
insert into @t exec(@s)
select * from @t -- where hh = 9/*******************hh num
----------- -----------
1 100
2 100
3 100
4 100
5 100
6 100
7 100
8 100
9 100
10 100
11 100
12 100
13 100
14 100
15 100
16 100
17 100
18 100
19 100
20 100
21 100
22 100
23 100
0 100(24 行受影响)我纠结的三楼,没看到么?
DECLARE @str VARCHAR(8000)
SET @str='01-101,02-102,03-103,04-104,05-105,06-106,07-107,'
DECLARE @a VARCHAR(10)
SET @a='03'
IF (SELECT CHARINDEX(','+@a+'-',','+@str))>0
BEGIN
SELECT RIGHT(SUBSTRING(@str,CHARINDEX(','+@a+'-',','+@str),6),LEN(SUBSTRING(@str,CHARINDEX(','+@a+'-',','+@str),6))-CHARINDEX('-',SUBSTRING(@str,CHARINDEX(','+@a+'-',','+@str),6)))
END------------------
(无列名)
103