数据筛选的SQL语句问题!!
哎呀
好久没来这里啦,分也散得差不多咯。555...
今天来提个问题!~
有1----10十个数(每次随机抽5个按序排列)
表ta里已有选取的记录:
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等)
------解决方案--------------------抽出了的数加起来不在表中.
如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)
------解决方案--------------------先把所有组合 存在临时表中 然后筛选
------解决方案--------------------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)