请大家帮忙看看这个嵌套循环要怎么做

zhaoanle 2006-02-09 10:54:28
/*大家看看这个嵌套循环,本来是希望能够在12345这五个数中任取三个数

/*ff
----------
111
112
113
114
115

（所影响的行数为 5 行）

111
112
113
114
115
121
122
...
211
212
...
554
555

*/
create table #tb(ff varchar(10))
declare @char varchar(8000)
declare @i int
declare @j int
declare @k int
declare @a varchar(1)
declare @b varchar(1)
declare @c varchar(1)
set @char='12345'
set @i=1
set @j=1
set @k=1

while @i<=len(@char)
begin
set @a=substring(@char,@i,1)
set @i=@i+1
while @j<=len(@char)
begin
set @b=substring(@char,@j,1)
set @j=@j+1
while @k<=len(@char)
begin
set @c=substring(@char,@k,1)
set @k=@k+1
insert #tb select @a+@b+@c
end
end
end

select * from #tb

...全文
70 点赞 收藏 7

7 条回复

samfeng_2003 2006-02-09
create table t
(col varchar(20))

declare @t varchar(50)
set @t='12345'
declare @t1 table(col varchar(50))
declare @i int
set @i=len(@t)
while @i>0
begin
insert @t1(col) values (left(@t,1))
select @t=stuff(@t,1,1,''),@i=@i-1
end

insert t
select a.col+b.col+c.col from @t1 a,@t1 b,@t1 c

select * from t order by col

drop table t

col
--------------------
111
112
113
114
115
……
554
555

（所影响的行数为 125 行）

zhaoanle 2006-02-09

declare @t1 table(id int)
declare @t2 table(id int)
declare @t3 table(id int)

insert into @t1 select 1
insert into @t1 select 2
insert into @t1 select 3
insert into @t1 select 4
insert into @t1 select 5

insert into @t2 select * from @t1
insert into @t3 select * from @t1

select * from @t1 a,@t2 b,@t3 c
order by a.id,b.id,c.id

samfeng_2003 2006-02-09
SOrry,这是刚才上午回你帖子的时候的！

pbsql 2006-02-09
create table #tb(ff varchar(10))
declare @char varchar(8000)
declare @i int
declare @j int
declare @k int
declare @a varchar(1)
declare @b varchar(1)
declare @c varchar(1)
set @char='12345'
set @i=1
while @i<=len(@char)
begin
set @a=substring(@char,@i,1)
set @i=@i+1
set @j=1
while @j<=len(@char)
begin
set @b=substring(@char,@j,1)
set @j=@j+1
set @k=1
while @k<=len(@char)
begin
set @c=substring(@char,@k,1)
set @k=@k+1
insert #tb select @a+@b+@c
end
end
end
--@i、@j、@k的赋值语句放错位置了，应该在while循环之前，即循环之后要恢复为1

samfeng_2003 2006-02-09
create table t
(col int)

declare @i int,@j int,@k int
select @i=1
while @i<=9
begin
set @j=1
while @j<=9
begin
set @k=1
while @k<=9
begin
if not (@i=@j or @i=@k or @k=@j)
insert t(col) values (@i*100+@j*10+@k)
set @k=@k+1
end
set @j=@j+1
end
set @i=@i+1
end

select * from t

drop table t

zhaoanle 2006-02-09

2.1w+

MS-SQL Server 疑难问题

2006-02-09 10:54