高难度的数据筛选问题!!!!!!

噯卟釋手 2007-03-12 11:36:06
看来提问标题要起好呀

不然都没有人气滴(最后50分咯 555...)

-------------------------------------------------------

有1----10十个数(每次随机抽5个按序排列)

表ta里已有选取的记录:
表ta的字段就是 tid aa bb cc dd ee 均为int型的 tid自增

tid aa bb cc dd ee
1 1 3 7 8 9 第一次抽取13789
2 4 6 7 8 10 第二次抽取46789
3 3 5 7 8 9 第三次抽取35789

问:如何列出还那些没有出现过的组合啊?
(肯定有很多,如12345,23456等)

--------------------------------------------------------
回答完这个问题得了分以后去这里
http://community.csdn.net/Expert/topic/5389/5389714.xml?temp=.5449335
同样的问题 一样结帖给分咯~~
...全文
236 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2007-03-12
  • 打赏
  • 举报
回复
a b c d e
----------- ----------- ----------- ----------- -----------
1 1 1 1 1
1 1 1 1 2
1 1 1 1 3
1 1 1 1 4
1 1 1 1 5
1 1 1 1 6
1 1 1 1 7
1 1 1 1 8
..........................
(所影响的行数为 99997 行)
1 1 1 1 9
1 1 1 1 10
1 1 1 2 1
1 1 1 2 2
1 1 1 2 3
1 1 1 2 4
1 1 1 2 5
1 1 1 2 6
1 1 1 2 7
1 1 1 2 8
1 1 1 2 9
1 1 1 2 10
dawugui 2007-03-12
  • 打赏
  • 举报
回复
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
dawugui 2007-03-12
  • 打赏
  • 举报
回复
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
declare @aa as int
declare @bb as int
declare @cc as int
declare @dd as int
declare @ee 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
while @d <= 10
begin
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
*/
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

噯卟釋手 2007-03-12
  • 打赏
  • 举报
回复
红尘大哥一来

问题应该迎刃而解了

呵呵
wangdehao 2007-03-12
  • 打赏
  • 举报
回复

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 * 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
)t
left join @t1 m
on t.aa=m.aa and t.bb=m.bb and t.cc=m.cc and t.dd=m.dd and t.ee=m.ee
where m.aa is null
dawugui 2007-03-12
  • 打赏
  • 举报
回复
要写循环逐一判断了,麻烦.我试试看.
子陌红尘 2007-03-12
  • 打赏
  • 举报
回复
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)
噯卟釋手 2007-03-12
  • 打赏
  • 举报
回复
wait wait wait~~
噯卟釋手 2007-03-12
  • 打赏
  • 举报
回复
多谢各位大哥咯~~

结帖咯~~

随便把我那个帖也结了~~

34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧