SQL如何把表中一个范围的数据提取处理

发布网友发布时间：2022-04-11 17:54

共1个回答

热心网友时间：2022-04-11 19:24

create table aaa (num1 int,num2 int);
insert into aaa values (1,3);
insert into aaa values (6,9);

--竖版
create function dbo.func_d (@a int)
RETURNS @table table(id int) as
begin

declare @num1 int,@num2 int;
declare mycursor cursor for
select * from aaa;
open mycursor
fetch mycursor into @num1,@num2;
while @@fetch_status=0
begin
while @num1<=@num2
begin
insert into @table values (@num1);
set @num1=@num1+1;
end;
fetch mycursor into @num1,@num2;
end;
return;
end

select * from dbo.func_d(1)
--结果：
id
1
2
3
6
7
8
9

----------------------------------
--横版
create function dbo.func_d2 (@a int)
RETURNS @table table(string varchar(4000)) as
begin

declare @num1 int,@num2 int;
insert into @table values ('a');
declare mycursor cursor for
select * from aaa;
open mycursor
fetch mycursor into @num1,@num2;
while @@fetch_status=0
begin
while @num1<=@num2
begin
update @table set string=string+','+convert(varchar,@num1);
set @num1=@num1+1;
end;
fetch mycursor into @num1,@num2;
end;
update @table set string=stuff(string,1,2,N'');

return;
end

select * from dbo.func_d2(1)
--结果：
string
1,2,3,6,7,8,9