with vHgInfoTable as
(
select a.l_hg_date,
a.l_redeem_lawdate,
a.l_redeem_liquidate,
a.l_settle_date,
a.l_redeal_date,
a.l_settle_speed,
a.c_entrust_direction,
max( case when b.c_entrust_direction in ('15','16','17','18','26','27','30',
'31','35','36','37','38','39','40')
then
a.L_REDEEM_DAYS
else
nvl((to_date(a.l_settle_date, 'YYYYMMDD') -
(select min(to_date(tm.l_date, 'YYYYMMDD'))
from tmarkettradeday tm
where tm.l_Date > a.l_hg_date
and tm.vc_tradeday_type =
j.vc_tradeday_type
and tm.c_trade_flag In ('1', '3'))),0)
end) l_use_days,
sum(a.l_deal_amount) l_deal_amount,
sum(decode(b.c_fund_direction, '1', 1, -1) *
a.en_deal_balance - a.en_fee) en_net_zj,
sum(decode(b.c_entrust_direction, '5', -1, 1) *
a.en_now_interest) en_now_interest,
sum(decode(b.c_fund_direction, '1', -1, 1) *
(a.en_deal_balance + a.en_redeem_interest)) en_ret_zj,
sum(decode(b.c_fund_direction, '1', -1, 1) *
a.en_redeem_interest - a.en_fee) en_profit,
sum(a.en_redeem_interest - a.en_fee -
(a.en_deal_balance + a.en_fee) *
nvl((select en_ratio
from (select en_year_rate / l_days en_ratio,
l_fund_id,
l_org_id
from TINTERESTRATE
where vc_currency_no = 'CNY'
and l_rate_type = '1'
order by l_org_id desc, l_fund_id desc) vi
where ((vi.l_fund_id = a.l_fund_id) or
(vi.l_fund_id = -1))
and ((vi.l_org_id = tfd.l_org_id) or
(vi.l_org_id = -1))
and rownum = 1),
0) * decode(a.l_redeem_days,
3,
decode(to_number(to_char(to_date(to_char(a.l_date,
99999999),
'yyyymmdd'),
'd')),
2,
3,
3,
5,
4,
5,
5,
4,
6,
1,
a.l_redeem_days),
a.l_redeem_days)) en_extra_profit,
min(b.vc_entrustdir_name) vc_entrustdir_name,
a.vc_inter_code,
(case
when count(distinct(a.l_fund_id)) = 1 then
min(a.l_fund_id)
else
-1
end) l_fund_id,
(case
when count(distinct(a.vc_stockholder_id)) = 1 then
min(a.vc_stockholder_id)
else
'混合'
end) vc_stockholder_id,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(e.vc_combi_no)
else
'混合'
end) vc_combi_no,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(a.l_basecombi_id)
else
-1
end) l_combi_id,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(e.vc_combi_name)
else
'混合'
end) vc_combi_name,
(case
when count(distinct(e.l_asset_id)) = 1 then
min(e.l_asset_id)
else
-1
end) l_asset_id,
'' vc_asset_name,
'' vc_asset_no,
(case
when count(distinct(a.l_operator_no)) = 1 then
min(a.l_operator_no)
else
-1
end) l_operator_no,
a.c_redeal_flag,
decode(sum(a.l_deal_amount),
0,
0,
sum(a.en_deal_price * a.l_Deal_Amount) /
sum(a.l_deal_amount)) en_avgInterest,
(case
when count(distinct(tfd.l_org_id)) = 1 then
min(tfd.l_org_id)
else
-1
end) l_org_id,
'' vc_org_name,
'' vc_org_code,
'' VC_CAPITAL_ACCOUNT,
min(j.vc_market_name) vc_market_name,
sum(decode(b.c_fund_direction, '1', -1, 1) *
a.en_redeem_interest) en_redeem_interest,
sum(a.en_fee) en_fee
from THGREGISTER a,
TENTRUSTDIRECTION b,
TMARKETINFO J,
TCOMBI e,
tfundinfo tfd
where a.c_entrust_direction = b.c_entrust_direction
and a.c_market_no = b.c_market_no
and a.c_market_no = j.c_market_no
and a.l_basecombi_id = e.l_combi_id
and a.l_fund_id = tfd.l_fund_id
and tfd.l_org_id in ( tfd.l_org_id )
and a.C_STOCK_TYPE in ( a.c_stock_type )
and a.c_market_no in ('1', '2')
and not exists
(select l_hgregister_serial_no
from TDELAYDEALHGREGISTER t
where t.l_hgregister_serial_no = a.l_serial_no
and t.l_fund_id in ( a.l_fund_id )
and t.l_basecombi_id in ( e.l_combi_id )
and t.vc_stockholder_id in ( a.vc_stockholder_id )
and ((t.vc_inter_code = '-1' ) or
( '-1' = '-1'))
and ((t.l_hg_date between '-1' and '-1' ) or
( '-1' = '-1')))
and a.l_fund_id in ( a.l_fund_id )
and a.vc_stockholder_id in ( a.vc_stockholder_id )
-- and e.l_asset_id in ( ^sAssetId )
and e.l_combi_id in ( e.l_combi_id )
and ((a.vc_inter_code = '-1' ) or ( '-1' = '-1'))
and ((e.c_combi_status = '1' ) or
( '1' = '-1'))
and ((a.l_hg_date between '-1' and '-1' ) or
( '-1' = '-1'))
and ((a.l_redeem_lawdate between '-1' and '-1' ) or
( '-1' = '-1'))
and ((a.l_redeem_liquidate between '-1' and
'-1' ) or ( '-1' = '-1'))
and ((a.c_redeal_flag <> '1') or ( '-1' = '-1'))
and ((a.c_redeal_flag = '0') or
((a.c_redeal_flag = '1') and
(a.l_redeem_liquidate = '20250220' )) or
( '0' = '-1'))
and ((a.c_redeal_flag = '0') or
((a.c_redeal_flag = '1') and
(a.l_redeem_liquidate < '20250220' )) or
( '-1' = '-1'))
and a.C_ENTRUST_DIRECTION in ('5','6','15','16')
and (select count(*)from topfundright where topfundright.l_asset_id = e.l_asset_id and topfundright.c_layer = '2' and topfundright.l_operator_no = 1000 and instr(topfundright.vc_rights, '1') > 0) > 0
group by a.l_hg_date,
a.l_redeem_lawdate,
a.l_redeem_liquidate,
a.l_settle_date,
a.l_redeal_date,
a.l_settle_speed,
a.vc_inter_code,
a.C_ENTRUST_DIRECTION,
a.c_redeal_flag
,a.l_fund_id ,e.l_asset_id ,a.vc_stockholder_id ,a.l_operator_no
union all
select a.L_NEW_HG_DATE l_hg_date,
a.l_redeem_lawdate,
a.L_NEW_REDEEM_LIQUIDATE l_redeem_liquidate,
a.l_settle_date,
a.l_redeal_date,
a.l_settle_speed,
a.c_entrust_direction,
max( case when b.c_entrust_direction in ('15','16','17','18','26','27','30',
'31','35','36','37','38','39','40')
then
a.L_REDEEM_DAYS
else
nvl((to_date(a.l_settle_date, 'YYYYMMDD') -
(select min(to_date(tm.l_date, 'YYYYMMDD'))
from tmarkettradeday tm
where tm.l_Date > a.L_NEW_HG_DATE
and tm.vc_tradeday_type =
j.vc_tradeday_type
and tm.c_trade_flag In ('1', '3'))),0)
end) l_use_days,
sum(a.l_deal_amount) l_deal_amount,
sum(decode(b.c_fund_direction, '1', 1, -1) *
a.en_deal_balance - a.en_fee) en_net_zj,
sum(decode(b.c_entrust_direction, '5', -1, 1) *
a.en_now_interest) en_now_interest,
sum(decode(b.c_fund_direction, '1', -1, 1) *
(a.en_deal_balance + a.en_redeem_interest)) en_ret_zj,
sum(decode(b.c_fund_direction, '1', -1, 1) *
a.en_redeem_interest - a.en_fee) en_profit,
sum(a.en_redeem_interest - a.en_fee -
(a.en_deal_balance + a.en_fee) *
nvl((select en_ratio
from (select en_year_rate / l_days en_ratio,
l_fund_id,
l_org_id
from TINTERESTRATE
where vc_currency_no = 'CNY'
and l_rate_type = '1'
order by l_org_id desc, l_fund_id desc) vi
where ((vi.l_fund_id = a.l_fund_id) or
(vi.l_fund_id = -1))
and ((vi.l_org_id = tfd.l_org_id) or
(vi.l_org_id = -1))
and rownum = 1),
0) * decode(a.l_redeem_days,
3,
decode(to_number(to_char(to_date(to_char(a.l_date,
99999999),
'yyyymmdd'),
'd')),
2,
3,
3,
5,
4,
5,
5,
4,
6,
1,
a.l_redeem_days),
a.l_redeem_days)) en_extra_profit,
min(b.vc_entrustdir_name) vc_entrustdir_name,
a.vc_inter_code,
(case
when count(distinct(a.l_fund_id)) = 1 then
min(a.l_fund_id)
else
-1
end) l_fund_id,
(case
when count(distinct(a.vc_stockholder_id)) = 1 then
min(a.vc_stockholder_id)
else
'混合'
end) vc_stockholder_id,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(e.vc_combi_no)
else
'混合'
end) vc_combi_no,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(a.l_basecombi_id)
else
-1
end) l_combi_id,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(e.vc_combi_name)
else
'混合'
end) vc_combi_name,
(case
when count(distinct(e.l_asset_id)) = 1 then
min(e.l_asset_id)
else
-1
end) l_asset_id,
'' vc_asset_name,
'' vc_asset_no,
(case
when count(distinct(a.l_operator_no)) = 1 then
min(a.l_operator_no)
else
-1
end) l_operator_no,
a.c_redeal_flag,
decode(sum(a.l_deal_amount),
0,
0,
sum(a.en_deal_price * a.l_Deal_Amount) /
sum(a.l_deal_amount)) en_avgInterest,
(case
when count(distinct(tfd.l_org_id)) = 1 then
min(tfd.l_org_id)
else
-1
end) l_org_id,
'' vc_org_name,
'' vc_org_code,
'' VC_CAPITAL_ACCOUNT,
min(j.vc_market_name) vc_market_name,
sum(decode(b.c_fund_direction, '1', -1, 1) *
a.en_redeem_interest) en_redeem_interest,
sum(a.en_fee) en_fee
from TDELAYDEALHGREGISTER a,
TENTRUSTDIRECTION b,
TMARKETINFO J,
TCOMBI e,
tfundinfo tfd,
tstockinfo ts
where a.c_entrust_direction = b.c_entrust_direction
and a.c_market_no = b.c_market_no
and a.c_market_no = j.c_market_no
and a.l_basecombi_id = e.l_combi_id
and a.l_fund_id = tfd.l_fund_id
and tfd.l_org_id in ( tfd.l_org_id )
and a.c_market_no in ('1', '2')
and a.l_fund_id in ( a.l_fund_id)
and a.VC_INTER_CODE = ts.VC_INTER_CODE
and ts.C_STOCK_TYPE in ( ts.c_stock_type )
and a.vc_stockholder_id in ( a.vc_stockholder_id )
-- and e.l_asset_id in ( ^sAssetId )
and e.l_combi_id in ( e.l_combi_id )
and ((a.vc_inter_code = '-1' ) or ( '-1' = '-1'))
and ((e.c_combi_status = '1' ) or
( '1' = '-1'))
and ((a.L_NEW_HG_DATE between '-1' and '-1') or
( '-1' = '-1'))
and ((a.l_redeem_lawdate between '-1' and '-1' ) or
( '-1' = '-1'))
and ((a.L_NEW_REDEEM_LIQUIDATE between '-1' and
'-1' ) or ( '-1' = '-1'))
and ((a.c_redeal_flag <> '1') or ( '-1' = '-1'))
and ((a.c_redeal_flag = '0') or
((a.c_redeal_flag = '1') and
(a.L_NEW_REDEEM_LIQUIDATE = '20250220' )) or
( '0' = '-1'))
and ((a.c_redeal_flag = '0') or
((a.c_redeal_flag = '1') and
(a.L_NEW_REDEEM_LIQUIDATE < '20250220' )) or
( '-1' = '-1'))
and a.C_ENTRUST_DIRECTION in ('5','6','15','16')
and (select count(*)from topfundright where topfundright.l_asset_id = e.l_asset_id and topfundright.c_layer = '2' and topfundright.l_operator_no = 1000 and instr(topfundright.vc_rights, '1') > 0) > 0
group by a.L_NEW_HG_DATE,
a.l_redeem_lawdate,
a.L_NEW_REDEEM_LIQUIDATE,
a.l_settle_date,
a.l_redeal_date,
a.l_settle_speed,
a.vc_inter_code,
a.C_ENTRUST_DIRECTION,
a.c_redeal_flag
,a.l_fund_id ,e.l_asset_id ,a.vc_stockholder_id ,a.l_operator_no
union all
select a.l_hg_date,
a.l_redeem_lawdate,
a.l_redeem_liquidate,
a.l_settle_date,
a.l_redeal_date,
a.l_settle_speed,
a.c_entrust_direction,
max( case when b.c_entrust_direction in ('15','16','17','18','26','27','30',
'31','35','36','37','38','39','40')
then
a.L_REDEEM_DAYS
else
nvl((to_date(a.l_settle_date, 'YYYYMMDD') -
(select min(to_date(tm.l_date, 'YYYYMMDD'))
from tmarkettradeday tm
where tm.l_Date > a.l_hg_date --20160527 20160531
and tm.vc_tradeday_type =
j.vc_tradeday_type
and tm.c_trade_flag In ('1', '3'))),0)
end) l_use_days,
sum(a.l_deal_amount) l_deal_amount,
sum(decode(b.c_fund_direction, '1', 1, -1) *
a.en_deal_balance - a.en_fee) en_net_zj,
sum(decode(b.c_entrust_direction, '5', -1, 1) *
a.en_now_interest) en_now_interest,
sum(decode(b.c_fund_direction, '1', -1, 1) *
(a.en_deal_balance + a.en_redeem_interest)) en_ret_zj,
sum(decode(b.c_fund_direction, '1', -1, 1) *
a.en_redeem_interest - a.en_fee) en_profit,
sum(a.en_redeem_interest - a.en_fee -
(a.en_deal_balance + a.en_fee) *
nvl((select en_ratio
from (select en_year_rate / l_days en_ratio,
l_fund_id,
l_org_id
from TINTERESTRATE
where vc_currency_no = 'CNY'
and l_rate_type = '1'
order by l_org_id desc, l_fund_id desc) vi
where ((vi.l_fund_id = a.l_fund_id) or
(vi.l_fund_id = -1))
and ((vi.l_org_id = tfd.l_org_id) or
(vi.l_org_id = -1))
and rownum = 1),
0) * decode(a.l_redeem_days,
3,
decode(to_number(to_char(to_date(to_char(a.l_date,
99999999),
'yyyymmdd'),
'd')),
2,
3,
3,
5,
4,
5,
5,
4,
6,
1,
a.l_redeem_days),
a.l_redeem_days)) en_extra_profit,
min(b.vc_entrustdir_name) vc_entrustdir_name,
a.vc_inter_code,
(case
when count(distinct(a.l_fund_id)) = 1 then
min(a.l_fund_id)
else
-1
end) l_fund_id,
(case
when count(distinct(a.vc_stockholder_id)) = 1 then
min(a.vc_stockholder_id)
else
'混合'
end) vc_stockholder_id,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(e.vc_combi_no)
else
'混合'
end) vc_combi_no,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(a.l_basecombi_id)
else
-1
end) l_combi_id,
(case
when count(distinct(a.l_basecombi_id)) = 1 then
min(e.vc_combi_name)
else
'混合'
end) vc_combi_name,
(case
when count(distinct(e.l_asset_id)) = 1 then
min(e.l_asset_id)
else
-1
end) l_asset_id,
'' vc_asset_name,
'' vc_asset_no,
(case
when count(distinct(a.l_operator_no)) = 1 then
min(a.l_operator_no)
else
-1
end) l_operator_no,
a.c_redeal_flag,
decode(sum(a.l_deal_amount),
0,
0,
sum(a.en_deal_price * a.l_Deal_Amount) /
sum(a.l_deal_amount)) en_avgInterest,
(case
when count(distinct(tfd.l_org_id)) = 1 then
min(tfd.l_org_id)
else
-1
end) l_org_id,
'' vc_org_name,
'' vc_org_code,
'' VC_CAPITAL_ACCOUNT,
min(j.vc_market_name) vc_market_name,
sum(decode(b.c_fund_direction, '1', -1, 1) *
a.en_redeem_interest) en_redeem_interest,
sum(a.en_fee) en_fee
from ThisHGREGISTER a,
TENTRUSTDIRECTION b,
TMARKETINFO J,
TCOMBI e,
tfundinfo tfd
where a.c_entrust_direction = b.c_entrust_direction
and a.c_market_no = b.c_market_no
and a.c_market_no = j.c_market_no
and a.l_basecombi_id = e.l_combi_id
and a.l_fund_id = tfd.l_fund_id
and tfd.l_org_id in ( tfd.l_org_id )
and a.c_market_no in ('1', '2')
and a.l_fund_id in ( a.l_fund_id )
and a.C_STOCK_TYPE in ( a.c_stock_type )
and a.vc_stockholder_id in ( a.vc_stockholder_id )
-- and e.l_asset_id in ( ^sAssetId )
and e.l_combi_id in ( e.l_combi_id )
and ((a.vc_inter_code = '-1' ) or ( '-1' = '-1'))
and ((e.c_combi_status = '1' ) or
( '1' = '-1'))
and ((a.l_hg_date between '-1' and '-1' ) or
( '-1' = '-1' ))
and ((a.l_redeem_lawdate between '-1' and '-1' ) or
( '-1' = '-1' ))
and ((a.l_redeem_liquidate between '-1' and
'-1' ) or ( '-1' = '-1'))
and '-1' = '1'
and ((a.c_redeal_flag <> '1') or ( '0' = '-1'))
and a.C_ENTRUST_DIRECTION in ('5','6','15','16')
and (select count(*)from topfundright where topfundright.l_asset_id = e.l_asset_id and topfundright.c_layer = '2' and topfundright.l_operator_no = 1000 and instr(topfundright.vc_rights, '1') > 0) > 0
group by a.l_hg_date,
a.l_redeem_lawdate,
a.l_redeem_liquidate,
a.l_settle_date,
a.l_redeal_date,
a.l_settle_speed,
a.vc_inter_code,
a.C_ENTRUST_DIRECTION,
a.c_redeal_flag
,a.l_fund_id ,e.l_asset_id ,a.vc_stockholder_id ,a.l_operator_no
)
select * from (select count(distinct rnum) over() as totalrn, rownum rn, tp.*
from (select count(0) over() as total,
dense_rank() over( order by t.l_fund_id,t.vc_inter_code desc,t.c_entrust_direction ) as rnum,
t.*
from (
select * from vHgInfoTable a order by a.l_fund_id,a.vc_inter_code desc,a.c_entrust_direction
) t
) tp
)t3
where t3.rnum >= 1
and t3.rnum <= 50
order by t3.rnum
详细说明上面sql，最后查出来的数据是什么条件过滤的从哪个表取得什么字段
扫一扫
6174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
