ORACLE SQL常用用法

1)行转列聚合

select trunc(f.month_wid / 100) as YEAR_WID,
       f.emp_wid,
       f.prodcat_wid,
       sum(decode(MOD(f.month_wid, 100), 1, f.call_num / f.totalcall_sr)) as JAN_VALUE,
       sum(decode(MOD(f.month_wid, 100), 2, f.call_num / f.totalcall_sr)) as FEB_VALUE,
       sum(decode(MOD(f.month_wid, 100), 3, f.call_num / f.totalcall_sr)) as MAR_VALUE,
       sum(decode(MOD(f.month_wid, 100), 4, f.call_num / f.totalcall_sr)) as APR_VALUE,
       sum(decode(MOD(f.month_wid, 100), 5, f.call_num / f.totalcall_sr)) as MAY_VALUE,
       sum(decode(MOD(f.month_wid, 100), 6, f.call_num / f.totalcall_sr)) as JUN_VALUE,
       sum(decode(MOD(f.month_wid, 100), 7, f.call_num / f.totalcall_sr)) as JUL_VALUE,
       sum(decode(MOD(f.month_wid, 100), 8, f.call_num / f.totalcall_sr)) as AUG_VALUE,
       sum(decode(MOD(f.month_wid, 100), 9, f.call_num / f.totalcall_sr)) as SEP_VALUE,
       sum(decode(MOD(f.month_wid, 100), 10, f.call_num / f.totalcall_sr)) as OCT_VALUE,
       sum(decode(MOD(f.month_wid, 100), 11, f.call_num / f.totalcall_sr)) as NOV_VALUE,
       sum(decode(MOD(f.month_wid, 100), 12, f.call_num / f.totalcall_sr)) as DEC_VALUE
  from WC_PROD_CONCALL_PRIOR_F f
  where f.int_org_wid=2148
 group by trunc(f.month_wid / 100), f.emp_wid, f.prodcat_wid

 季度：select trunc((MOD(trunc(20141201/100),100)-1)/3)+1 from dual

2）TO_CHAR函数

      TO_CHAR(SYSDATE -1,'YYYYMM')

      TO_CHAR(ADD_MONTHS(SYSDATE -1, -1), 'YYYYMM')

      TO_CHAR((SYSDATE-1), 'DD') <= 7

3)分析函数

SELECT DISTINCT CF.MONTH_WID,
       CF.OWNER_POSTN_WID,
       CF.X_SR_WID,
       --CF.X_PROD_LN_WID,
       PRODCAT.Row_Wid as PRODCAT_WID,
      -- cf.prod_wid,
       ORG.ROW_WID AS INT_ORG_WID,
       COUNT(DISTINCT CF.ROW_WID) OVER(PARTITION BY CF.MONTH_WID,CF.OWNER_POSTN_WID,CF.X_SR_WID,ORG.ROW_WID,PRODCAT.Row_Wid) AS CALL_NUM,
       COUNT(DISTINCT CF.ROW_WID) OVER(PARTITION BY CF.MONTH_WID,CF.OWNER_POSTN_WID,CF.X_SR_WID,ORG.ROW_WID)AS TOTALCALL_SR
  FROM W_CALL_CON_F    CF,
       W_LOV_D         PRI,
       WC_SUB_TGTCLS_D SUB,
       W_ACTIVITY_F    F,
       W_LOV_D         LOV,
       W_INT_ORG_D  ORG,
       W_PRODUCT_D PROD, 
       WC_PRODCAT_D PRODCAT
 WHERE CF.PRIORITY_WID = PRI.ROW_WID
   AND PRI.R_TYPE = 'RANK'
   AND PRI.VAL IN ('1', '2')
   AND CF.X_SUB_TGTCLS_WID = SUB.ROW_WID
   AND SUB.X_TARGET_CLASS IN ('Level 1', 'Level 2', 'Level 3')
   AND CF.PAR_INTEGRATION_ID = F.INTEGRATION_ID
   AND F.X_KPI_FLG_WID = LOV.ROW_WID
   AND LOV.NAME = 'Y'
   AND CF.X_VIS_BU_ID=ORG.INTEGRATION_ID
   --AND F.INTEGRATION_ID='1-B7EYH4'
   AND CF.MONTH_WID=TO_CHAR(SYSDATE -1,'YYYYMM')
   AND CF.PROD_WID=PROD.ROW_WID
   AND PROD.PRODUCT_TYPE_CODE='Product'
   AND PROD.PRODCAT_WID=PRODCAT.ROW_WID