oracle 的with as应用,一个with as的oracle应用

with tmp1 as (

select

t."ID",

t.userName,

t.productId,

t.operateId,

t.normalIds,

t.operateTime

from trsaaa_statisticripeData t

left join idsuser t2 on t.username=t2.username

where t.operateTime < (to_date(‘2014-01-01‘, ‘yyyy-mm-dd‘) -

to_date(‘1970-01-01‘ || TZ_OFFSET(sessiontimezone), ‘yyyy-mm-dd hh:mi‘)) * 86400000

and t.operateTime > (to_date(‘2013-01-01‘, ‘yyyy-mm-dd‘) -

to_date(‘1970-01-01‘ || TZ_OFFSET(sessiontimezone), ‘yyyy-mm-dd hh:mi‘)) * 86400000

and t.statisticType = ‘TRADING‘

),

tmp2 as (

select t.entry_id entry_id

from [email protected] t start with t.entry_id in (‘52‘)

connect by t.parent_id = prior t.entry_id

),

tmp3 as (

select distinct

tmp1."ID",

tmp1.userName,

tmp1.productId,

tmp1.operateId,

Min(tmp1.operateTime - t4.BEGINTIME) as touchTime

from tmp1 left join TRSAAA_CONTRACT t4

on (tmp1.username = t4.username and (tmp1.operateTime between t4.BEGINTIME and t4.ENDTIME or (tmp1.operateTime > t4.BEGINTIME and t4.ENDTIME = 0)))

left join tmp2 on (1=1)

where ((tmp1.normalIds like ‘%,‘ || tmp2.entry_id) or (tmp1.normalIds like ‘%,‘ || tmp2.entry_id || ‘,%‘))

group by tmp1."ID",

tmp1.userName,

tmp1.productId,

tmp1.operateId

)

select distinct

t.userName as 用户账号,

t.nickName as 用户名称,

t3.typename as 用户类型,

case when t4."CHARGINGWAY" = ‘ITEM‘ then

‘按条计费‘

when t4."CHARGINGWAY" = ‘KIND‘ then

‘按类计费‘

when t4."CHARGINGWAY" = ‘NO‘ then

‘不计费‘

when t4."CHARGINGWAY" = ‘AFTER‘ then

‘后付费‘

else

t4."CHARGINGWAY"

end as 计费类型,

case t.VALIDDATA when 1 then ‘有效‘ else ‘无效‘ end as 交易有效性,

t.normalids as normalids,

t.feeid as feeid,

t.feepath as feepath,

to_char( TO_DATE(‘01/01/1970 ‘|| TZ_OFFSET(sessiontimezone), ‘MM/DD/YYYY HH:MI‘) +

operateTime / (1000 * 60 * 60 * 24),‘YYYY/MM/DD‘) as 交易时间,

t.productId as 稿件ID,

t.productTitle as 稿件标题,

t.operateId as 交易流水号,

t.price as 交易价格,

t.statisticType as 交易类型

from TRSAAA_STATISTICRIPEDATA t

inner join tmp3 on t."ID" = tmp3."ID"

left join TRSAAA_USERTYPEMAPPING t3 on t.username=t3.username

left join TRSAAA_CONTRACT t4 on (t.username = t4.username and t.operateTime - t4.BEGINTIME = tmp3.touchTime);

原文：http://www.cnblogs.com/aneo/p/4119586.html