sql中參數(shù)過多利用變量替換參數(shù)的方法
發(fā)布日期：2021-12-29 16:50 | 文章來源：gibhub
復制代碼 代碼如下:
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!='交易類'
非常復雜，不仔細介紹，此時需要將20120801處全部變成參數(shù)傳入，，只寫參數(shù)就得累死我，下面方法可以節(jié)省我的好多工作量，在最內(nèi)部函數(shù)中修改select
b.scode,b.fund5,b.fund4,b.fund10,20120801 tdate
from
將20120801用tdate變量來代替，這樣傳入的參數(shù)就用tdate來表示就可以了。
版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信