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的最大值合并一行。求大神修改下！！！！！！！！！！
uj5u.com熱心網友回復：
如果可以去掉code欄位的話，可以按照objid和title分組 取 max(year),max(nvl(price,0)),min(nvl(eps,999999)
uj5u.com熱心網友回復：
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
沒有測驗過，樓主可以試看看
uj5u.com熱心網友回復：
是按照baseyear分組了但是沒有合并成一條。eps和price還是分開兩條顯示的
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/82495.html
標籤：開發
上一篇：oracle ocp