我用distinct去除重复项太慢,请问大家有什么好的办法。select distinct jgbm,cfh from sjpt_cfls
where HANDLETIME between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2
其中主要是 cfh的字段值非常长,而且表里面数据有2000多万条。
如:
jgbm cfh
0004 041F074AE05M03H-12041000689
0010 0Q050749A05D13B-12040900355
0024 0O0I074BE2IE003-12041100100
0010 0Q050749A05D13B-12040900355请问我应该如何写sql使他的执行效率变高
where HANDLETIME between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2
其中主要是 cfh的字段值非常长,而且表里面数据有2000多万条。
如:
jgbm cfh
0004 041F074AE05M03H-12041000689
0010 0Q050749A05D13B-12040900355
0024 0O0I074BE2IE003-12041100100
0010 0Q050749A05D13B-12040900355请问我应该如何写sql使他的执行效率变高
解决方案 »
- 诡异的ora-01400: 无法将 NULL 插入 ("SYSTEM"."HC_RLDYRBMXB_TEMP"."DYBH"
- 帮忙介绍下PRO C编程
- oracle安装问题(redhat5.3 + oracle10g)
- 请教oracle的ORA-01034和ORA-27101的修改方案
- 找不到oracle management server!?!?!?!?
- 关于SQL语句不懂的地方,请教
- 不做DBA学Oracle学到什么程度差不多?
- 简单的问题,如何用sql语句使满足某个条件的先显示??
- 能不能查看某一表空间下的所有对象?
- 使用sql语句将width和height替换成空
- 请教一个RMAN“show all”后出现异常退出的问题
- 关于在存储过程中使用参数作为插入数据条件时执行慢的问题……
jgbm,cfh这2个字段确定唯一?
jgbm cfh
0004 041F074AE05M03H-12041000689
0010 0Q050749A05D13B-120409003550024 0O0I074BE2IE003-12041100100
0010 0Q050749A05D13B-12040900355
select distinct jgbm,cfh from sjpt_cfls
where HANDLETIME a between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2 and EXISTS ( SELECT 1
FROM HANDLETIME b WHERE a.jgbm = b.jgbm)
select jgbm,cfh,count(*)
from sjpt_cfls
where HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2
group by jgbm,cfh
having count(*) = 1
jgbm cfh
0004 041F074AE05M03H-12041000689
0010 0Q050749A05D13B-12040900355
0024 0O0I074BE2IE003-12041100100
0010 0Q050749A05D13B-12040900355
where HANDLETIME a between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2 and EXISTS ( SELECT 1
FROM HANDLETIME b WHERE a.jgbm = b.jgbm)
低了,这个cfh字段值非常的长你可以看下我写的问题,这样效率太低太低
select jgbm,cfh
FROM sjpt_cfls a
where exists (select 1 from sjpt_cfls b where a.jgbm=b.jgbm) and
HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2
!
这位兄台连where后顺序都帮它调好,还让人家学习么!
--(低效)
SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); --(高效)
SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’
你说的很多要用 max()代替1
--假设主键或者唯一字段 s_id
select jgbm,cfh
FROM sjpt_cfls
where s_id in (select max(s_id) from sjpt_cfls group by jgbm,cfh) and
HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2