解决方案 »
- alter database open resetlogs的问题
- 怎么调用oracle创建存储过程导入txt文件
- 急! 我的oracle 10g 服务 和监听器 一开就自动关了 怎么办啊?
- 【请教:如何不truncate而重整表空间】
- 高手来看啊!select * from v$process where PROGRAM='ORACLE.EXE'谁知道怎样取其中一个的内存利用率!
- 存储过程中如何实现交互选择执行(可作为新手经典改错题)
- Oracle监听器问题
- 数据导入问题(imp)
- 每天自动执行存储过程或sql语句,有正确答案立即给分
- 急问: rman中的命令report总出错(ora-01843无效的月份)
- 数据库毕业设计 做个什么好呢
- oracle11g impdp导出报错
select p.category_name,p.num_value from (select t1.category_name category_name1, t1.min_value min_value1, t1.max_value max_value1,t2.category_name,t2.num_value from t1,t2 where t1.category_name = t2.category_name) p where p.num_value between p.min_value1 and p.max_value1;
select TABLE_2.category_name, TABLE_2.num_value from
(
select 'a' category_name,1 min_value,3 max_value from dual
union all select 'b' category_name,2 min_value,7 max_value from dual
union all select 'c' category_name,2 min_value,6 max_value from dual
) TABLE_1,
(
select 'a' category_name,2 num_value from dual
union all select 'a' category_name,6 num_value from dual
union all select 'b' category_name,8 num_value from dual
union all select 'c' category_name,4 num_value from dual
union all select 'c' category_name,3 num_value from dual
union all select 'c' category_name,5 num_value from dual) TABLE_2
where TABLE_1.category_name = TABLE_2.category_name
and TABLE_2.num_value between TABLE_1.min_value and TABLE_1.max_value
并在表2上建立的 category_name + Timeky的索引,表2的数据量很大,楼上的这个
SQL在表2是全表扫描的,时间效率上很差
执行计划t1也走了索引范围扫描了
select t2.category_name, t2.num_value
from t1, t2
where t1.category_name = t2.category_name
and t2.num_value between t1.min_value and t1.max_value;
with tb_a as (
select 'a' category_name, 1 min_value, 3 max_value from dual union all
select 'b' category_name, 2 min_value, 7 max_value from dual union all
select 'c' category_name, 2 min_value, 6 max_value from dual
), tb_b as (
select 'a' category_name,2 num_value from dual union all
select 'a' category_name,6 num_value from dual union all
select 'b' category_name,8 num_value from dual union all
select 'c' category_name,4 num_value from dual union all
select 'c' category_name,3 num_value from dual union all
select 'c' category_name,5 num_value from dual)
select *
from (select b.* from tb_a a left outer join tb_b b
on (a.category_name = b.category_name and a.min_value <= b.num_value and a.max_value >= b.num_value ) )
where category_name is not null;
select s.category_name,s.num_value from 表二 s left join 表一 d on s.category_name = d.category_name where s.num_value between d.min_value and d.max_value ;我觉得 这样 肯定 能达到效果,选择做链接 。
from tb1,tb2
where tb1.category_name=tb2.category_name
and (tb2.num_value between tb1.max_value and tb1.min_value)
and num_value between t1.min_value and t1.max_value;
from test_t2 a
where exists (select 1
from test_t1 b
where a.category_name = b.category_name
and a.num_value between b.min_value and b.max_value
);
(select test_1.*,num_value
from test_1,test_2 where test_1.category=test_2.category)
where num_value between min_value and max_value
create table A(
name varchar2(20),
minvalue int,
maxvalue int
);
create table B(
name varchar2(20),
numvalue int
);
insert into A values('a',1,3);
insert into A values('b',2,7);
insert into A values('c',2,6);
insert into B values('a',2);
insert into B values('a',6);
insert into B values('b',8);
insert into B values('c',4);
insert into B values('c',3);
insert into B values('c',5);
select b.name,b.numvalue from B b,A a where b.name=a.name and b.numvalue between a.minvalue and a.maxvalue;
select b.name,b.numvalue from B b join A a on b.name=a.name and b.numvalue between a.minvalue and a.maxvalue;结果: