Orcal语句查询

Question

select *
  from (select rt.objid,
               rt.title,
               sfd.forcastvalue,
               sfd.indicatorcode,
               rt.ratingvalue,
               to_char(sfd.forcasttime, 'yyyy') baseyear,
               (case sfd.indicatorcode
                 when '0011' then
                  sfd.forcastvalue
                 else
                  ''
               end) price,
               (case sfd.indicatorcode
                 when '0004' then
                  sfd.forcastvalue
                 else
                  ''
               end) eps
          from ir_report rt
          left join sr_stockforecastrelation sfr on sfr.sourceentity =
                                                    rt.entityname
                                                and sfr.sourceid = rt.objid
          left join sr_stockforecastdetail sfd on sfd.forecastid =
                                                  sfr.forecastid
        
         where rt.objid = 3111
           and sfd.indicatorcode in (0004, 0011)
         order by sfd.forcasttime)
执行效果 
        objid          title                code        ravalue      year       price       eps
1 3111       零·旗鼓D           0004      50          2016                    1.40
2 3111      零·旗鼓D             0011          50          2017       52
3 3111      零·旗鼓D            0004           50           2017                  1.00
我想要的效果
     objid          title                   code（可以去掉）                 ravalue   year       price       eps
1 3111      零·旗鼓D          0004/0011（可以去掉）           50        2017       52         1.00
就是根据objid并且去year的最大值合并一行。求大神修改下！！！！！！！！！！

Answer 1

如果可以去掉code字段的话，可以按照objid和title分组 取 max(year),max(nvl(price,0)),min(nvl(eps,999999)

Answer 2

select objid,title,forcastvalue,indicatorcode,ratingvalue,baseyear,max(price) price,max(eps) eps
  from (select rt.objid,
               rt.title,
               sfd.forcastvalue,
               sfd.indicatorcode,
               rt.ratingvalue,
               to_char(sfd.forcasttime, 'yyyy') baseyear,
               (case sfd.indicatorcode
                 when '0011' then
                  sfd.forcastvalue
                 else
                  ''
               end) price,
               (case sfd.indicatorcode
                 when '0004' then
                  sfd.forcastvalue
                 else
                  ''
               end) eps,
   rank() over (partition by rt.objid order by to_char(sfd.forcasttime, 'yyyy') desc) rn
          from ir_report rt
          left join sr_stockforecastrelation sfr on sfr.sourceentity =
                                                    rt.entityname
                                                and sfr.sourceid = rt.objid
          left join sr_stockforecastdetail sfd on sfd.forecastid =
                                                  sfr.forecastid
         
         where rt.objid = 3111
           and sfd.indicatorcode in (0004, 0011)
         order by sfd.forcasttime)
 where rn = 1
 group by objid,title,forcastvalue,indicatorcode,ratingvalue,baseyear
没有测试过，楼主可以试看看

Answer 3

是按照baseyear分组了但是没有合并成一条。eps和price还是分开两条显示的