问题提出: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
有图有真相