關於一些函數的用法．row_number()over(partition by)和case when then

SELECT * FROM (
  SELECT sg.opp_id, sg.sales_id,sg.old_sales_id,sg.id,
  Row_Number()over(PARTITION BY sg.opp_id ORDER BY sg.gmt_create desc) rwn,sg.gmt_create
FROM nirvana.sfa_opportunity so, nirvana.sfa_select_log sg WHERE
 so.id=sg.opp_id AND so.product_id_prefer='20'   AND sg.OPERATION='tosea' AND   sg.CAUSE='noContactOnTime'
AND  sg.OLD_SALES_ID IN ('hjzw0','hjzw1','hjzw2','hjzw3','hjzw4','hjzw5','hjzw6','hjzw7','hjzw8','hjzw9')
AND sg.gmt_create >= To_Date('2008-08-08 00:00:00','yyyy-MM-dd HH24:mi:ss') AND so.sales_id='hjzw10'
) t
WHERE rwn=1

 

 

SELECT so.sales_id,so.id,ad.org_id FROM nirvana.sfa_opportunity so,nirvana.admin_user ad
WHERE so.renew_flag='N' AND so.is_closed='N'
AND  Trunc((CASE when (Trunc(gmt_contact) >= Trunc(gmt_created) ) THEN gmt_contact
WHEN (Trunc(gmt_contact) <= Trunc(gmt_created) ) THEN gmt_created
ELSE   gmt_created
END))+1 <=(SYSDATE - 60)
AND so.product_id_prefer = 20
AND so.sales_id IN (SELECT login_id FROM nirvana.admin_user u WHERE u.org_id IN (SELECT o.id FROM nirvana.ali_org o WHERE o.full_real_name_path LIKE '/銷售/渠道銷售部%'))AND so.sales_id=ad.login_Id
AND so.sales_id NOT LIKE 'agent-%'