复制代码 代码如下:

select
ff.fundsc||\'-\'||ff.fundtzfs||\'-\'||ff.fundcjfl||\'-\'||ff.fundonefl||\'-\'||ff.fundtowfl catagory,
sf.scode,replace(sf.fund5,\'型证券投资基金\',\'\')fund5,sf.fund4,sf.fund10,
(select to_date(tradedate,\'yyyy-MM-dd\') from sdc_fundnetassetvalue where scode=sf.scode and tradedate in
(select max(tradedate) from sdc_fundnetassetvalue where scode=sf.scode and to_date(tradedate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and tradedate<=20120801) and rownum<2) trdatedate,
(select unitvalue from sdc_fundnetassetvalue where scode=sf.scode and tradedate in
(select max(tradedate) from sdc_fundnetassetvalue where scode=sf.scode and to_date(tradedate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and tradedate<=20120801) and rownum<2) fejz,
(select accumulatedunitvalue from sdc_fundnetassetvalue where scode=sf.scode and tradedate in
(select max(tradedate) from sdc_fundnetassetvalue where scode=sf.scode and to_date(tradedate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and tradedate<=20120801) and rownum<2) feljjz,
(select f30004_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2) gqyzjzzzl,
(select f30006_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2) gqyyjzzzl,
decode((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) gqsgyjzzzl,
row_number() over(partition by ff.fundsc||\'-\'||ff.fundtzfs||\'-\'||ff.fundcjfl||\'-\'||ff.fundonefl||\'-\'||ff.fundtowfl
order by decode((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30007_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) desc) sgy,
decode((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) gqbnjzzzl,
row_number() over(partition by ff.fundsc||\'-\'||ff.fundtzfs||\'-\'||ff.fundcjfl||\'-\'||ff.fundonefl||\'-\'||ff.fundtowfl
order by decode((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30008_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) desc) bn,
decode((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) jinnian,
row_number() over(partition by ff.fundsc||\'-\'||ff.fundtzfs||\'-\'||ff.fundcjfl||\'-\'||ff.fundonefl||\'-\'||ff.fundtowfl
order by decode((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select F30017_30003 from si_fund_30003 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30003 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) desc) jn,
decode((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) gqynjzzzl,
row_number() over(partition by ff.fundsc||\'-\'||ff.fundtzfs||\'-\'||ff.fundcjfl||\'-\'||ff.fundonefl||\'-\'||ff.fundtowfl
order by decode((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30009_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) desc) gqyn,
decode((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) gqlnjzzzl,
row_number() over(partition by ff.fundsc||\'-\'||ff.fundtzfs||\'-\'||ff.fundcjfl||\'-\'||ff.fundonefl||\'-\'||ff.fundtowfl
order by decode((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30010_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) desc) gqln,
decode((select f30011_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),
null,\'-\',to_char((select f30011_30002 from si_fund_30002 where f_fundcode=sf.scode and f_type=0 and f_tradingdate in
(select max(f_tradingdate) from si_fund_30002 where f_fundcode=sf.scode and to_date(f_tradingdate,\'yyyyMMdd\')>=(to_date(20120801,\'yyyyMMdd\')-10) and f_tradingdate<=20120801) and rownum<2),\'fm999999990.9999\')) gqsnjzzzl

from
(select
b.scode,b.fund5,b.fund4,b.fund10
from
sdc_fundbase b left join sdc_security a on b.scode=a.scode and b.mktcode=a.mktcode and b.fund49=a.stype
where
b.scode in (select t.fundcode from fund_fundcategoryone t where (t.fundtzfs=\'开放式\' or t.fundtzfs=\'封闭式\') and t.fundonefl!=\'货币市场基金\' and t.fundonefl!=\'交易类\' )
and (a.enddate>=to_date(20120801,\'yyyy-MM-dd\') or a.enddate is null or a.enddate=to_date(19000101,\'yyyy-MM-dd\'))
and a.stype in (6,7) order by scode
) sf
left join fund_fundcategoryone ff on sf.scode = ff.fundcode and ff.fundonefl!=\'交易类\'