ORACLE取中值解决办法

select station,freq,service_area,destination_place,avg(H_VALUE) from

(

(

select b.*

  from

       ( -- 计算中间一行的行号

       select station,freq,service_area,destination_place,decode(mod(max_rk, 2), -- 取余数

                     0,    max_rk / 2,      -- 偶数

                     trunc(max_rk / 2) + 1  -- 奇数

                     ) mid_rk

         from ( -- 计算行号

               select station,freq,service_area,destination_place,max(a1.rk) max_rk

                 from (-- 排序的结果集

                       select a11.*,

                               row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk

                          from effect_intense_info a11

                      ) a1 group by station,freq,service_area,destination_place

              )

       ) a  ,

       ( -- 排序的结果集

        select b1.*,

               row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk

          from effect_intense_info b1

       ) b

 where b.rk = a.mid_rk and b.station=a.station and b.freq=b.freq and a.service_area = b.service_area and a.destination_place = b.destination_place

 )

 union

(

select b.*

  from

       ( -- 计算中间一行的行号

       select station,freq,service_area,destination_place,decode(mod(max_rk, 2), -- 取余数

                     0,    max_rk / 2 + 1,      -- 偶数

                     trunc(max_rk / 2) + 1  -- 奇数

                     ) mid_rk

         from ( -- 计算行号

               select station,freq,service_area,destination_place,max(a1.rk) max_rk

                 from (-- 排序的结果集

                       select a11.*,

                               row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk

                          from effect_intense_info a11

                      ) a1 group by station,freq,service_area,destination_place

              )

       ) a  ,

       ( -- 排序的结果集

        select b1.*,

               row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk

          from effect_intense_info b1

       ) b

 where b.rk = a.mid_rk and b.station=a.station and b.freq=b.freq and a.service_area = b.service_area and a.destination_place = b.destination_place

)

) AA group by station,freq,service_area,destination_place