首页 > 代码库 > 一个with as的oracle应用
一个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 category_fl@NWKEDIT 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);
一个with as的oracle应用