sql - ORA-00907 : missing right parenthesis tips in Order by

Question

Order by !! 中缺少右括号提示

我想从 sal_sale_fct 得到最后的 amn_unit_pric_rial根据 shp.DAT_CALCULATE、shp.COD_PROD_SAPRB来自 shp_inventory_fct如果在 sal_sale_fct 中找不到行返回 sal_sale_fct 中最后日期的最后价格

select shp.DAT_CALCULATE,
      shp.COD_PROD_SAPRB,     
          (select sal.amn_unit_pric_rial
                  from sal_sale_fct sal 
                 where rownum = 1
                   and shp.cod_prod_saprb = sal.saprb_cod_prod_saprb
                   and shp.dat_calculate <= sal.dat_ord_ordhe
                   order by sal.dat_ord_ordhe
                   ) price
  From shp_inventory_fct shp

Answer 1

当您同时使用 rownum 和 order by 时，首先计算 rownum。因此第一行将不在 sal.dat_ord_ordhe 的 order by 中。所以你需要先排序，然后使用 rownum 选择第一行

select shp.DAT_CALCULATE,
  shp.COD_PROD_SAPRB,     
      (select price 
           From  (
             select sal.amn_unit_pric_rial price
              from sal_sale_fct sal 
             where shp.cod_prod_saprb = sal.saprb_cod_prod_saprb
               and shp.dat_calculate <= sal.dat_ord_ordhe
               order by sal.dat_ord_ordhe
               ) where  rownum = 1) Price
               From shp_inventory_fct shp