表格如下:id1 id2 num1 num2 id只有-1/0/1三种组合,num有1/0两种组合。希望找出不存在的排列组合
create table tb(id1 int,id2 int, num1 int,num2 int) insert into tb select -1, 0, 1, 0 union
select 0, 1, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 1, 0 union
select -1, 1, 0, 1 union
select 1, -1, 1, 0 union
select 1, 1, 0, 1 union
select -1, -1, 0, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select 0, 1, 1, 0 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 0, -1, 0, 1 union
select -1, -1, 1, 1 union
select 1, 0, 0, 1 union
select 1, 1, 1, 1 union
select -1, -1, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 1, 1, 0 union
select -1, 1, 0, 1 union
select 1, 0, 1, 1 union
select 1, 0, 0, 0 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, -1, 1, 0 union
select 0, -1, 1, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select -1, 1, 0, 1 union
select 0, 0, 1, 0 union
select 0, 1, 0, 1 union
select -1, 0, 1, 0 union
select 0, -1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 0, 0, 0 union
select 0, 0, 0, 1 union
select -1, 1, 1, 1 union
select -1, 0, 1, 0 union
select 1, 1, 1, 0 union
select 1, 0, 0, 1
create table tb(id1 int,id2 int, num1 int,num2 int) insert into tb select -1, 0, 1, 0 union
select 0, 1, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 1, 0 union
select -1, 1, 0, 1 union
select 1, -1, 1, 0 union
select 1, 1, 0, 1 union
select -1, -1, 0, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select 0, 1, 1, 0 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 0, -1, 0, 1 union
select -1, -1, 1, 1 union
select 1, 0, 0, 1 union
select 1, 1, 1, 1 union
select -1, -1, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 1, 1, 0 union
select -1, 1, 0, 1 union
select 1, 0, 1, 1 union
select 1, 0, 0, 0 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, -1, 1, 0 union
select 0, -1, 1, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select -1, 1, 0, 1 union
select 0, 0, 1, 0 union
select 0, 1, 0, 1 union
select -1, 0, 1, 0 union
select 0, -1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 0, 0, 0 union
select 0, 0, 0, 1 union
select -1, 1, 1, 1 union
select -1, 0, 1, 0 union
select 1, 1, 1, 0 union
select 1, 0, 0, 1
解决方案 »
- 把tb2中的marketId ,StockId不存在于tb1中的记录插入到tb1中。这样写么???
- 一个存储过程执行的时候 提示日期转化错误!
- 求一比较复杂问题
- 就是insert 中的values的值可以是select中查询出来的吗? 急急
- 这个问题如何解决?谢谢各位大哥
- 不允许从数据类型 text 到 nvarchar 的隐性转换
- 再开一贴,求一SQL语句?
- jdbc连接数据库执行存储过程tomcat outOfMemory
- 如何使用account intelligence 在2005?
- 帮我看一下这句话那里不对?(在线等待)
- SQL关于10和1排序混乱问题!!!!!!!!!!!!!谢谢!!!
- 有个触发器语句,要从ASA9转换到SQL2000,请大侠帮忙
-------------
好像没说清楚哟
id1=-1 and id2=0 and num1=0 and num2=1
--不知道是不是這個意思?
create table tb(id1 int,id2 int, num1 int,num2 int)
insert into tb
select -1, 0, 1, 0 union
select 0, 1, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 1, 0 union
select -1, 1, 0, 1 union
select 1, -1, 1, 0 union
select 1, 1, 0, 1 union
select -1, -1, 0, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select 0, 1, 1, 0 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 0, -1, 0, 1 union
select -1, -1, 1, 1 union
select 1, 0, 0, 1 union
select 1, 1, 1, 1 union
select -1, -1, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 1, 1, 0 union
select -1, 1, 0, 1 union
select 1, 0, 1, 1 union
select 1, 0, 0, 0 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, -1, 1, 0 union
select 0, -1, 1, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select -1, 1, 0, 1 union
select 0, 0, 1, 0 union
select 0, 1, 0, 1 union
select -1, 0, 1, 0 union
select 0, -1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 0, 0, 0 union
select 0, 0, 0, 1 union
select -1, 1, 1, 1 union
select -1, 0, 1, 0 union
select 1, 1, 1, 0 union
select 1, 0, 0, 1
create table #t(id int)
insert into #t values(-1)
insert into #t values(0)
insert into #t values(1)select T1.*
from
(--找出全部組合
select A.id as id1,B.id as id2 ,C.id as num1,D.id as num2
from #t A
full join #t B on 1=1
full join (select * from #t where id in(0,1) ) C on 1=1
full join (select * from #t where id in(0,1) ) D on 1=1
) T1
left join tb
on T1.id1=tb.id1 and T1.id2=tb.id2 and T1.num1=tb.num1 and T1.num2=tb.num2
where tb.id1 is null/*
id1 id2 num1 num2
-----------------------------
-1 -1 0 0
-1 1 0 0
0 -1 0 0
0 1 0 0
1 -1 0 0
1 1 0 0
-1 1 1 0
0 -1 1 0
-1 0 0 1
1 -1 0 1
-1 0 1 1
0 0 1 1
0 1 1 1
1 -1 1 1*/drop table tb,#t
create table #(id1 int,id2 int, num1 int,num2 int)
insert into #
select -1, 0, 1, 0 union
select 0, 1, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 1, 1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 1, 0 union
select -1, 1, 0, 1 union
select 1, -1, 1, 0 union
select 1, 1, 0, 1 union
select -1, -1, 0, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select 0, 1, 1, 0 union
select 1, 0, 1, 0 union
select 1, 0, 0, 1 union
select 0, -1, 0, 1 union
select -1, -1, 1, 1 union
select 1, 0, 0, 1 union
select 1, 1, 1, 1 union
select -1, -1, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 1, 1, 0 union
select -1, 1, 0, 1 union
select 1, 0, 1, 1 union
select 1, 0, 0, 0 union
select -1, 0, 0, 0 union
select 0, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, -1, 1, 0 union
select 0, -1, 1, 1 union
select 0, 0, 1, 0 union
select -1, -1, 0, 1 union
select -1, 1, 0, 1 union
select 0, 0, 1, 0 union
select 0, 1, 0, 1 union
select -1, 0, 1, 0 union
select 0, -1, 0, 1 union
select 1, 0, 1, 0 union
select 0, 0, 0, 1 union
select -1, 0, 1, 0 union
select -1, 0, 1, 0 union
select 0, 0, 0, 0 union
select 0, 0, 0, 1 union
select -1, 1, 1, 1 union
select -1, 0, 1, 0 union
select 1, 1, 1, 0 union
select 1, 0, 0, 1/*
表格如下:
id1 id2 num1 num2
id只有-1/0/1三种组合,num有1/0两种组合。
希望找出不存在的排列组合
*/select * from
(select -1id1 union all select 0 union all select 1) a,
(select -1id2 union all select 0 union all select 1) b,
(select 0num2 union all select 1) c,
(select 0num3 union all select 1) d
except
select * from #
在关键字 'except' 附近有语法错误。
select a.*,b.*,c.*,d.* from
(select -1id1 union all select 0 union all select 1) a
cross join
(select -1id2 union all select 0 union all select 1) b
cross join
(select 0num1 union all select 1) c
cross join
(select 0num2 union all select 1) d
left join # e
on a.id1=e.id1 and b.id2=e.id2 and c.num1=e.num1 and d.num2=e.num2
where e.id1 is null
insert into tb
select -1, 0, 1, 0 union
select 0, 1, 0, 1 union
select 1, 0, 1, 0 union create table #t(id int)
insert into #t values(-1)
insert into #t values(0)
insert into #t values(1)create table #T2(ID int)
insert #t2 select 0
insert #t2 select 1select *
from
#t t1,#t t2,#T2 t3,#T2 t4
where
checksum(*) not in(select checksum(*) from tb)