用存储过程, 2排 3个,便是存储过程的两个参数在存储过程中定义计数变量,用insert into 来循环插入数据
declare @a table(排 int) declare @b table(号 int)declare @i int --排 declare @j int --号 set @i=2 set @j=3 set nocount on while @i>0 begin insert @a select @i set @i=@i-1 end while @j>0 begin insert @b select @j set @j=@j-1 end select id=identity(int,1,1),* into #t from @a,@b order by 排,号 select * from #tdrop table #t
declare @a table(排 int) declare @b table(号 int)declare @i int --排 declare @j int --号 set @i=3 ---排设置处 set @j=5 ---号设置处 set nocount on while @i>0 begin insert @a select @i set @i=@i-1 end while @j>0 begin insert @b select @j set @j=@j-1 end select id=identity(int,1,1),* into #t from @a,@b order by 排,号 select * from #tdrop table #tid 排 号 ----------- ----------- ----------- 1 1 1 2 1 2 3 1 3 4 1 4 5 1 5 6 2 1 7 2 2 8 2 3 9 2 4 10 2 5 11 3 1 12 3 2 13 3 3 14 3 4 15 3 5
if OBJECT_ID('tb')is not null drop table tb if OBJECT_ID('pro_c') is not null drop procedure pro_c go create table tb (id int,排号 int) go create procedure pro_c (@id int,@id1 int) as set nocount on declare @var int set @var=@id1 while @id>0 begin set @id1=@var while @id1>0 begin insert into tb select @id,@id1 set @id1=@id1-1 end set @id=@id-1 end set nocount off go exec pro_c 2,3 select * from tb order by id,排号 id 排号 ----------- ----------- 1 1 1 2 1 3 2 1 2 2 2 3(6 行受影响)
select id=identity(int,1,1), a.排, b.号 into #t from (select top 2 --输入排 id as 排 from sysobjects) a, (select top 3 --输入号 id as 号 from sysobjects) b --取结果 select * from #t --删除临时表 drop table #t-->结果 id 排 号 ----------- ----------- ----------- 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 2 3(所影响的行数为 6 行)
-->存储过程 create proc sp_test @i int, --排 @j int --号 as set nocount on declare @a table(排 int) declare @b table(号 int) while @i>0 begin insert @a select @i set @i=@i-1 end while @j>0 begin insert @b select @j set @j=@j-1 end select id=identity(int,1,1),* into #t from @a,@b order by 排,号 select * from #tdrop table #tgoexec sp_test 2,3-->结果 id 排 号 ----------- ----------- ----------- 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 2 3(所影响的行数为 6 行)
declare @T table(R1 int,R2 int) declare @r1 int,@r2 int,@1 int,@2 int select @r1=2,@r2=3,@1=1 while @1<=@r1 begin set @2=1 while @2<=@r2 begin insert @T(r1,r2) values(@1,@2) set @2=@2+1 end set @1=@1+1 end select * from @t /* R1 R2 ----------- ----------- 1 1 1 2 1 3 2 1 2 2 2 3 */
2排 3个,便是存储过程的两个参数在存储过程中定义计数变量,用insert into 来循环插入数据
declare @b table(号 int)declare @i int --排
declare @j int --号
set @i=2
set @j=3
set nocount on
while @i>0
begin
insert @a select @i
set @i=@i-1
end
while @j>0
begin
insert @b select @j
set @j=@j-1
end select id=identity(int,1,1),* into #t from @a,@b order by 排,号 select * from #tdrop table #t
declare @b table(号 int)declare @i int --排
declare @j int --号
set @i=3 ---排设置处
set @j=5 ---号设置处
set nocount on
while @i>0
begin
insert @a select @i
set @i=@i-1
end
while @j>0
begin
insert @b select @j
set @j=@j-1
end select id=identity(int,1,1),* into #t from @a,@b order by 排,号 select * from #tdrop table #tid 排 号
----------- ----------- -----------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 1
7 2 2
8 2 3
9 2 4
10 2 5
11 3 1
12 3 2
13 3 3
14 3 4
15 3 5
drop table tb
if OBJECT_ID('pro_c') is not null
drop procedure pro_c
go
create table tb (id int,排号 int)
go
create procedure pro_c
(@id int,@id1 int)
as
set nocount on
declare @var int
set @var=@id1
while @id>0
begin
set @id1=@var
while @id1>0
begin
insert into tb select @id,@id1
set @id1=@id1-1
end
set @id=@id-1
end
set nocount off
go
exec pro_c 2,3
select * from tb order by id,排号
id 排号
----------- -----------
1 1
1 2
1 3
2 1
2 2
2 3(6 行受影响)
id=identity(int,1,1),
a.排,
b.号
into #t
from
(select top 2 --输入排
id as 排 from sysobjects) a,
(select top 3 --输入号
id as 号 from sysobjects) b
--取结果
select * from #t
--删除临时表
drop table #t-->结果
id 排 号
----------- ----------- -----------
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3(所影响的行数为 6 行)
create proc sp_test
@i int, --排
@j int --号
as
set nocount on
declare @a table(排 int)
declare @b table(号 int)
while @i>0
begin
insert @a select @i
set @i=@i-1
end
while @j>0
begin
insert @b select @j
set @j=@j-1
end select id=identity(int,1,1),* into #t from @a,@b order by 排,号 select * from #tdrop table #tgoexec sp_test 2,3-->结果
id 排 号
----------- ----------- -----------
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3(所影响的行数为 6 行)
declare @r1 int,@r2 int,@1 int,@2 int
select @r1=2,@r2=3,@1=1
while @1<=@r1
begin
set @2=1
while @2<=@r2
begin
insert @T(r1,r2) values(@1,@2)
set @2=@2+1
end
set @1=@1+1
end
select * from @t
/*
R1 R2
----------- -----------
1 1
1 2
1 3
2 1
2 2
2 3
*/