已知:一个TABLE有9个整型字段,且字段的值按照从小到大排列,另外TABLE有一个序列号作为主键的字段,现给定9个已知数字
要求的一条SQL语句:
选择该表中有5个字段等于已给定的9个已知数字的序列号。
要求的一条SQL语句:
选择该表中有5个字段等于已给定的9个已知数字的序列号。
解决方案 »
- windows下Oracle Rman增量备份和恢复
- ORACLE环境问题--急需解决
- oracle触发器语句 version加一 迅速结贴
- Oracle连接java报错
- 请问Orcale数据库中怎么实现完全UTF8字符集的存取?问题重大而普遍,请大家各抒己见
- 在oracle中如何设置数据库的时区(DBTIMEZONE)?
- 微软震荡波补丁包似乎与oracle冲突,请问如何解决,急
- 想成为ORACLE DBA需要看什么书呢?
- oracle提供不提供API函数,如何应用?立即给分!
- Orale远程数据库的连接
- 嵌套查询
- oracle里面,action调用ibatis里面我想传一个不是form的参数值,sql如何写啊?
Table 9个整形字段
F1,F2,F3,F4,F5,F6,F7,F8,F9
1个序号字段S19个整形数字
1,2,3,4,5,6,7,8,9要求:
1.F1 in (1,2,3,4,5,6,7,8,9)
2.F2 in (1,2,3,4,5,6,7,8,9)
3.F3 in (1,2,3,4,5,6,7,8,9)
4.F4 in (1,2,3,4,5,6,7,8,9)
5.F5 in (1,2,3,4,5,6,7,8,9)
6.F6 in (1,2,3,4,5,6,7,8,9)
7.F7 in (1,2,3,4,5,6,7,8,9)
8.F8 in (1,2,3,4,5,6,7,8,9)
9.F9 in (1,2,3,4,5,6,7,8,9)
当上面的9个条件,有5个满足时,即得到这条记录是不是这样啊?
select 序列号
from table
where
(case when F1 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F2 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F3 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F4 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F5 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F6 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F7 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F8 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when F9 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)>=5
create table tb01(f1 number,f2 number, f3 number, f4 number, f5 number, f6 number, f7 number, f8 number, f9 number);
insert into tb01 select 1,2,3,4,5,6,7,8,9 from dual;
insert into tb01 select 11,2,3,4,5,61,71,81,90 from dual;
insert into tb01 select 11,22,33,4,5,61,71,81,90 from dual;
insert into tb01 select 11,2,3,4,5,6,71,81,90 from dual;
create table tb02(sno number,descr varchar2(2000));
insert into tb02 select 1,'测试数据1' from dual;
insert into tb02 select 2,'测试数据2' from dual;
insert into tb02 select 3,'测试数据3' from dual;
insert into tb02 select 4,'测试数据4' from dual;
我有点疑问问下:假如已经给定9个数字为 1,2,3,4,5,6,7,8,9(最好别重复,不然就麻烦了。) 标记为fnumber91,条件表tb01中5个字段等于已经给定的9个数字fnumber9的话;2,那么你查tb2表的序列号;这2者之间如何结合起来?没有什么逻辑啊,比方说你查完tb01表了,有3条记录符合你的要求,那么之后,有什么条件查tb02表的序列号呢?
“一个TABLE有9个整型字段,且字段的值按照从小到大排列,另外TABLE有一个序列号作为主键的字段”
对于你的5个字段等于已给定的9个已知数字的判断可以如下sql解决。select '序列号',tb01.*
from tb01
where
(case when f1 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f2 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f3 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f4 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f5 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f6 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f7 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f8 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f9 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)=5;
/
SQL> '序列号' F1 F2 F3 F4 F5 F6 F7 F8 F9
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
序列号 11 2 3 4 5 6 71 81 90'序列号' F1 F2 F3 F4 F5 F6 F7 F8 F9
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
序列号
你的这个理解很有道理,不然,就没法做了!另外TABLE指的就是前一个table吧!同一张表吧!
insert into tb03 select 111,1,2,3,4,5,6,7,8,9 from dual;
insert into tb03 select 222,11,2,3,4,5,61,71,81,90 from dual;
insert into tb03 select 333,11,22,33,4,5,61,71,81,90 from dual;
insert into tb03 select 444,11,2,3,4,5,6,71,81,90 from dual;select tb03.sno 序列号,tb03.*
from tb03
where
(case when f1 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f2 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f3 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f4 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f5 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f6 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f7 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f8 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)+
(case when f9 in (1,2,3,4,5,6,7,8,9) then 1 else 0 end)=5 序列号 SNO F1 F2 F3 F4 F5 F6 F7 F8 F9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
444 444 11 2 3 4 5 6 71 81 90
from tb03 where
decode(f1,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f2,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f3,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f4,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f5,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f6,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f7,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f8,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)+
decode(f9,1,1, 2,1, 3,1, 4,1, 5,1, 6,1, 7,1, 8,1, 9,1, 0)=5;