Question

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

比如表：tmp_a
字段：strat_num，end_num
记录： 1 ， 4
6 ， 8
11 ， 13

想把strat_num和end_num间的数据查询出来，
结果是：1，2，3，4，6，7，8，11，12，13

请问有没有什么内置函数可以处理，或者提供个函数或存储过程参考一下。
谢谢

Answer 1

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