现在有一SQL语句如下:
select b.enterprisenum,count(distinct(a.docno)),count(distinct(c.docno))
from ic_imageofroll a ,ic_roll_define b ,ic_list c
where a.rollid=b.rollid and b.enterprisenum=c.boxno and b.rollstatus='ROLL'
and substr(b.enterprisenum,16,2)='01'
group by b.enterprisenum
having count(distinct(a.docno))<>count(distinct(c.docno))
运行结果是正确的,但是速度太慢了~~~~~
例如:b.enterprisenum count(distinct(a.docno)) count(distinct(c.docno))
gf-2002-510100 45 50但是下面这句SQL语句运行却报错!
select b.enterprisenum,count(distinct(a.docno)) ,count(distinct(c.docno))
from ic_imageofroll a ,ic_roll_define b ,ic_list c
where a.rollid=b.rollid and b.enterprisenum=c.boxno and b.rollstatus='ROLL' and substr(b.enterprisenum,16,2)='01' and count(distinct(a.docno))<>count(distinct(c.docno))
报错内容为:“ORA:00934:此处不允许使用分组函数”
请各位高手指点一下~~不胜感激~~~~~~
select b.enterprisenum,count(distinct(a.docno)),count(distinct(c.docno))
from ic_imageofroll a ,ic_roll_define b ,ic_list c
where a.rollid=b.rollid and b.enterprisenum=c.boxno and b.rollstatus='ROLL'
and substr(b.enterprisenum,16,2)='01'
group by b.enterprisenum
having count(distinct(a.docno))<>count(distinct(c.docno))
运行结果是正确的,但是速度太慢了~~~~~
例如:b.enterprisenum count(distinct(a.docno)) count(distinct(c.docno))
gf-2002-510100 45 50但是下面这句SQL语句运行却报错!
select b.enterprisenum,count(distinct(a.docno)) ,count(distinct(c.docno))
from ic_imageofroll a ,ic_roll_define b ,ic_list c
where a.rollid=b.rollid and b.enterprisenum=c.boxno and b.rollstatus='ROLL' and substr(b.enterprisenum,16,2)='01' and count(distinct(a.docno))<>count(distinct(c.docno))
报错内容为:“ORA:00934:此处不允许使用分组函数”
请各位高手指点一下~~不胜感激~~~~~~
from ic_roll_define b join (select rollid,docno,count(docno) docno from ic_imageofroll group by rollid,docno) a
on b.rollid=a.rollid
join (select boxno,docno,count(docno) from ic_list group by boxno,docno) c
on b.enterprisenum=c.boxno and a.docno<> c.docno
where b.rollstatus='ROLL' and substring(b.enterprisenum,16,2)='01'
group by b.enterprisenum
substr(b.enterprisenum,16,2)='01'
把这个独立成一个字段吧(在insert时就写到一个字段中),然后在语句中就不使用substr了其次,你根据你的查询语句用的字段建相应的索引再次,你把 b.rollstatus='ROLL'
and substr(b.enterprisenum,16,2)='01'
b表符合这个条件的先查出来放到一个临时表中,你的那个查询再用临时表
看这样能否优化一下你查询的速度
http://www.mystruggle.com.cnhttp://www.mybuffet.cn