各位大侠, 小弟遇到一个亟待解决的问题,搞不定求助,问题如下:
1
8 2
7 3
6 4
5
组成一个循环,我想从任意一个数开始读取31个数,例如我从3开始,那结果是3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1用sql如何实现。谢谢
1
8 2
7 3
6 4
5
组成一个循环,我想从任意一个数开始读取31个数,例如我从3开始,那结果是3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1用sql如何实现。谢谢
select (@temp + 30)%8 --第一个数已经有了,所以是往后数30个连续数
@End INT = 3+8+31-1
WHILE @Star <= @End
BEGIN
SELECT
CASE WHEN @Star % 8 = 0 THEN 8
ELSE @Star % 8
END AS Num
SET @Star = @Star +1
END
declare @i int =1,@output varchar(100) ='',@input int=3--默认为3
while @i<32
begin
if @input<9 and @input%8<>0
begin
set @output=@output+cast(@input as varchar(10))+','
set @input=@input+1
end
else
begin
set @output=@output+cast(@input as varchar(10))+','
set @input=1
end
set @i=@i+1
end
print(@output)
go
--方法2
declare @t1 table(rowId int,n int)
declare @t2 table(输入 int,输出 varchar(100))
declare @i int =1
declare @output varchar(100)='',@intput int=3--默认为3
while @i<32
begin
set nocount on
insert into @t1 values(@i,case when @i%8>0 then @i%8 else 8 end)
set @i=@i+1
end
set nocount off
select @output=@output+cast(n as varchar(10))+',' from(
select n from @t1 where rowId>=@intput
union all
select case when (n+7)%8=0 then 8 else (n+7)%8 end from @t1 where rowId<@intput
) as T
print(@output)
----------------------------结果---------------------------
/*
3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8,1,
*/