日期:2014-05-19  浏览次数:20631 次

高难度的数据筛选问题!!!!!!
看来提问标题要起好呀  

不然都没有人气滴(最后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
同样的问题     一样结帖给分咯~~

------解决方案--------------------
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)
------解决方案--------------------
要写循环逐一判断了,麻烦.我试试看.
------解决方案--------------------

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