这是oracle的问题还是我写的sql的问题？求sql达人给看一看

Question

select *
  from gisductseg
 where sign((select nvl(decode(count(c),
                               0,
                               0,
                               sum(decode(sign(24 - c), 1, 1, 0)) / count(c)),
                        0) cc
               from (select count(f.id) c
                       from giscablesectionrelation cr, gisfiber f
                      where gisductseg.id = cr.edgeid
                        and cr.cablesectionid = f.cablesectionid
                      group by cr.cablesectionid)) - 0.126) = 1
这个sql，在我的数据库里运行结果完全不对甚至在我们的正式库里直接报错，提示gisductseg.id无法找到。create or replace function getUnderUtilizationDuctseg(segid in number) return number is
  result number(18,15) ;
begin    select nvl(decode(count(c),
                          0,
                          0,
                          sum(decode(sign(24 - c), 1, 1, 0)) / count(c)),
                   0) into result
          from (select count(f.id) c
                  from giscablesectionrelation cr, gisfiber f
                 where segid = cr.edgeid
                   and cr.cablesectionid = f.cablesectionid
                 group by cr.cablesectionid);  return result;
end getUnderUtilizationDuctseg;select * from gisductseg where getUnderUtilizationDuctseg(gisductseg.id)>0.126这样，通过把嵌套提取到function后，结果就是正确的了谁帮忙来解答一下啊，不甚感激

Answer 1

where gisductseg.id = cr.edgeid   ?你都没有这个表?gisductseg

Answer 2

select *
  from gisductseg
where sign最开头这里有这个表，在嵌套的最外层

Answer 3

应该这样，就可以了！
select * 
from gisductseg 
where sign(
(
select nvl(decode(count(c), 0, 0, 
                              sum(decode(sign(24 - c), 1, 1, 0)) / count(c)
      ), 
                        0) cc 
              from (
      select count(f.id) c 
                      from giscablesectionrelation cr, gisfiber f ,gisductseg
                      where gisductseg.id = cr.edgeid 
                        and cr.cablesectionid = f.cablesectionid 
                      group by cr.cablesectionid
      ) ) - 0.126) = 1 ;
红色部分是你少写的内容。

Answer 4

确实很奇怪呀。把sign去掉，也会有这个错误么。是

Answer 5

是因为嵌套太多了，最里面的那层查询认不到gisductseg 
我认为这个代码有很大优化的空间

Answer 6

select * from  gisductseg where(
                 select count(case when count(1)<24 then 1 end)
                      /count(count(1))
                    from giscablesectionrelation cr, gisfiber f 
                      where gisductseg.id = cr.edgeid 
                        and cr.cablesectionid = f.cablesectionid
                        group by cr.cablesectionid)>0.126这样应该就行了吧