分组中查询不符合条件的组

问题提出：oracle爱好者qq群里的snowg

select CORP_TYPE_NAME,CRRP_NAME,GROWTH_CODE,GROWTH_NAME,GROWTH_DATE,STATION_NAME

from SERVICE.WEB_CNAN_ABAM

 where growth_date>'20100101' and growth_date<'20120101' and STATION_NAME='垫江'

 ORDER BY 2,3

 这是要求分组的表，其中STATION_NAME站点会有很多（上千），后面的CORP_TYPE_CODE物种大类和下面的表的TYPE_CODE关联

 问题需求是统计中各个站点（上千）下不同物种（15类）发育不完整的记录组，比如这里的TYPE_CODE=1的稻类有9个过程。

 2012-11-12 21:00:58 更新：snowg终于qq在线，测试ok

SELECT AA.STATION_NAME,AA.CORP_TYPE_CODE,AA.CORP_TYPE_NAME,AA.C1,BB.C2
 FROM
 
(select  STATION_NAME,CORP_TYPE_CODE,CORP_TYPE_NAME,COUNT(*) C1
from SERVICE.WEB_CNAN_ABAM
 where growth_date>'20100101' and growth_date<'20120101' 
GROUP BY  STATION_NAME,CORP_TYPE_CODE,CORP_TYPE_NAME) AA,

(SELECT TYPE_CODE,TYPE_NAME,COUNT(*) C2 FROM BASE.OBSERVE_ABAM_DICT_GROWTH GROUP BY TYPE_CODE,TYPE_NAME) BB

WHERE AA.CORP_TYPE_CODE=BB.TYPE_CODE
AND AA.C1<BB.C2
ORDER BY AA.STATION_NAME,AA.CORP_TYPE_CODE

 

 下面是我写的sql，问题应该解决，我把需要分析的组作为一个表aa，然后基准表作为bb，使用两者之间的关系

aa.s2=bb.s1 来确保对比的是每一个地区下每一个种类下的每一个物种

select aa.glorgbookcode,aa.glorgbookname,aa.s2,aa.c2
  from  (select glorgbookcode,glorgbookname , substr(subjcode,1,4) s2,
                       count(*) c2
                  from  bd_accsubj,bd_glorgbook 
                 where bd_accsubj.pk_glorgbook=bd_glorgbook.pk_glorgbook  
  
                       and( subjcode like '5502%' or subjcode like '4104%' )
 and glorgbookcode not like '%0003'
                 group by  glorgbookcode,glorgbookname,substr(subjcode,1,4)
                 order by glorgbookcode)aa,
       (select substr(subjcode,1,4) s1,  count(*)c1 from bd_accsubj,bd_glorgbook  where
 bd_accsubj.pk_glorgbook=bd_glorgbook.pk_glorgbook and
 glorgbookcode='01-0001'
 and( subjcode like '5502%' or subjcode like '4104%' )
  group by  glorgbookcode,glorgbookname,substr(subjcode,1,4)  ) bb
  where aa.s2=bb.s1 and aa.c2<bb.c1
        

有图有真相