September 2007 - Posts

• Oracle竖变横一句通

  设有如下表 T A   B ----------- 1   01 1   02 1   03 2   04 2   05 3   06 执行如下 sql select a,replace(max(sys_connect_by_path(b,';')),';','') b  from (select a,b,  (row_number() over(order by a,b)  + dense_rank() over(order by a)) rn,  min(b) over(partition by a) mb  from t  )  start with b = mb  connect by rn-1 = prior rn  group by a 可得如下结果 A   B -------------------- 1   010203 2   0405 3   06 Posted Sep 19 2007, 02:32 PM by wego with no comments Filed under: Oracle

• ORACLE 的 GROUP BY ROLLUP 与 ratio_to_report 激情碰撞

  假设有如下表: SALE_AMOUNT BU      AMOUNT ============= CRD             100 SHK             200 RSJ               500   运行如下 sql SELECT   DECODE (GROUPING_ID (BU), 1, 'Total', BU)  as  BU,         SUM (AMOUNT)  as  AMOUNT,         (ratio_to_report (SUM (AMOUNT)) OVER ()) * 2  as  AMOUNT_RATIO    FROM SALE_AMOUNTGROUP BY ROLLUP (BU) ORDER BY BU   可得如下结果 BU    AMOUNT           AMOUNT_RATIO ================================ CRD            100                               0.0125 RSJ              500                                 0.625 SHK            200                                   0.25 Total            800                                        1    当它们激情碰撞时, 有两点是需要注意的: 1)      ratio_to_report (SUM (AMOUNT)) OVER () 中的SUM () 须写于内层而不能写于外层. 如SUM (ratio_to_report (AMOUNT) OVER ())   2)      ratio_to_report 后须乘以 2, 因为ratio_to_report (SUM (AMOUNT)) OVER () 已除多一个 total 了. 即AMOUNT / (2  *  TOTAL_ AMOUNT )   Posted Sep 12 2007, 04:44 PM by wego with no comments