if object_id('pubs..tb') is not null
drop table tb
go
create table tb
(
tid int,
aa int,
bb int,
cc int,
dd int,
ee int
)
insert into tb(tid,aa,bb,cc,dd,ee) values(1, 1, 3, 7, 8, 9)
insert into tb(tid,aa,bb,cc,dd,ee) values(2, 4, 6, 7, 8, 10 )
insert into tb(tid,aa,bb,cc,dd,ee) values(3, 3, 5, 7, 8, 9)
create table test
(
a int,
b int,
c int,
d int,
e int
)
declare @a as int
declare @b as int
declare @c as int
declare @d as int
declare @e as int
set @a = 1
set @b = 1
set @c = 1
set @d = 1
set @e = 1
WHILE @a <= 10
begin
set @b = 1
while @b <= 10
begin
set @c = 1
while @c <= 10
begin
set @d = 1
while @d <= 10
begin
set @e = 1
while @e <= 10
begin
insert into test(a,b,c,d,e) values(@a,@b,@c,@d,@e)
set @e = @e + 1
end
set @d = @d + 1
end
set @c = @c + 1
end
set @b = @b + 1
end
set @a = @a + 1
end
select * from test where cast(a as varchar) + cast(b as varchar) + cast(c as varchar) + cast(d as varchar) + cast(e as varchar) not in
(select cast(aa as varchar) + cast(bb as varchar) + cast(cc as varchar) + cast(dd as varchar) + cast(ee as varchar) from tb)
drop table tb,test
declare @t1 table(tid int,aa int,bb int,cc int,dd int,ee int)
insert into @t1 select 1,1,3,7,8,9
insert into @t1 select 2,4,6,7,8,10
insert into @t1 select 3,3,5,7,8,9
declare @t2 table(id int)
insert into @t2
select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8
union select 9 union select 10
select
n.*
from
(select
a.id aa,b.id bb,c.id cc,d.id dd,e.id ee
from
@t2 a,@t2 b,@t2 c,@t2 d,@t2 e
where
a.id<b.id and b.id<c.id and c.id<d.id and d.id<e.id) n
where
not exists(select 1 from @t1 where aa=n.aa and bb=n.bb and cc=n.cc and dd=n.dd and ee=n.ee)
如select ..... from where cast(a as varchar) + cast(b as varchar) + cast(c as varchar) + cast(d as varchar) + cast(e as varchar) no in (select cast(aa as varchar)+cast(bb as varchar)+cast(cc as varchar)+cast(dd as varchar)+cast(ee as varchar) from ta)
按照楼主的要求a,b,c,d,e应该是不重复的5个数字所以修改部分 老乌龟的代码
WHILE @a <= 6
begin
set @b = @a+1
while @b <= 7
begin
set @c = @b+1
while @c <=8
begin
set @d = @c+1
while @d <= 9
begin
set @e = @d+1
while @e <= 10
begin
insert into test(a,b,c,d,e) values(@a,@b,@c,@d,@e)
set @e = @e + 1
end
set @d = @d + 1
end
set @c = @c + 1
end
set @b = @b + 1
end
set @a = @a + 1
end