SQl

分页查询：

SELECT * FROM Account t1
WHERE ID IN
(
SELECT TOP 30 ID FROM
(
SELECT TOP 90 ID FROM Account ORDER BY ID ASC
) t ORDER BY t.ID DESC
)
ORDER BY t1.ID

视图：

v_Contract
select type=1, side=0,strike=0, a.PF_Code as pfcode,a.Period as period,a.Price as price,a.V as iv,a.SR_Price_Scan as ps,a.SR_Volatility_Scan as vs,a.Ra_01 as r1,a.Ra_02 as r2,a.Ra_03 as r3,a.Ra_04 as r4,a.Ra_05 as r5,a.Ra_06 as r6,a.Ra_07 as r7,a.Ra_08 as r8,a.Ra_09 as r9,a.Ra_10 as r10,a.Ra_11 as r11,a.Ra_12 as r12,a.Ra_13 as r13,a.Ra_14 as r14,a.Ra_15 as r15,a.Ra_16 as r16 from v_Future as a
union
select type=2,a.Option_Type as side,a.Strike_Price as strike, f.PF_Code as pfcode,s.Period as period,a.Price as price,a.V as iv,s.SR_Volatility_Scan as ps,s.SR_Volatility_Scan as vs,a.Ra_01 as r1,a.Ra_02 as r2,a.Ra_03 as r3,a.Ra_04 as r4,a.Ra_05 as r5,a.Ra_06 as r6,a.Ra_07 as r7,a.Ra_08 as r8,a.Ra_09 as r9,a.Ra_10 as r10,a.Ra_11 as r11,a.Ra_12 as r12,a.Ra_13 as r13,a.Ra_14 as r14,a.Ra_15 as r15,a.Ra_16 as r16 from [Option] as a left join Series as s on s.ID =a.Series_ID left join v_Future as f on s.UC_ID=f.ID

v_Future
select distinct Future.*,pf.*,m.CName as MarketName,m.CName,ex.DispExch_CName,ex.Risk_Mode from Future left join (Product as p left join (Product_Family as pf left join Combined_Commodity as cc on pf.CC_Code=cc.Code left join Market as m on pf.Market_ID=m.ID left join Exchange as ex on pf.Exch_Code=ex.Code) on p.PF_Code=pf.PF_Code) on Future.Product_ID=p.ID

v_HisMargin
select Product.PF_Code as pfcode, cur.Factor as factor, His_Margin.Date as name,His_Margin.MaxVal as value from His_Margin left join Product on Product.ID=His_Margin.ProductID LEFT JOIN Currency_Conversion AS cur on Product.Settle_Currency=cur.To_Currency

v_HisVix
select (CONVERT(varchar(100), Date, 23)+' '+CONVERT(varchar(100), Time, 8)) as stime,CONVERT(varchar(100), Date, 23) as sdate,* from His_VIX

v_Inter
select l.EC_Code as ec, l.CC_Code as cc,l.Spread as spread,l.Relative_Side as side,type='Delta',l.Ratio as ratio,s.Rate_Value as rateValue,t.Start_Period as startDate, t.End_Period as endDate from Inter_Tier_Leg as l left join Inter_Tier as t on l.EC_Code=t.EC_Code and l.CC_Code=t.CC_Code and l.Inter_Tier_Number=t.Tier_Number left join Inter_CS_Delta as s on l.EC_Code=s.EC_Code and l.Spread=s.Spread
union
select l.EC_Code as ec, l.CC_Code as cc,l.Spread as spread,l.Relative_Side as side,type='Scan',l.Ratio as ratio,s.Rate_Value as rateValue,t.Start_Period as startDate, t.End_Period as endDate from Inter_Tier_Leg as l left join Inter_Tier as t on l.EC_Code=t.EC_Code and l.CC_Code=t.CC_Code and l.Inter_Tier_Number=t.Tier_Number left join Inter_CS_Scan as s on l.EC_Code=s.EC_Code and l.Spread=s.Spread

v_Inter2
select i.*
, case isnull(d.ec_code, '') when '' then '' else 'Delta' end
+ case isnull(s.ec_code, '') when '' then '' else 'Scan' end as type
, isnull(d.Rate_Value,0)
+ isnull(s.Rate_Value,0) as rate_value,x.pfcode,x.currency,p.Start_Period,p.End_Period,m.CName
from Inter_Tier_Leg i
inner join (
select distinct l.EC_Code,l.CC_Code, Spread,pf.PF_Code as pfcode,pf.Currency_Code as currency,pf.Market_ID as mkID
from Inter_Tier_Leg l
inner join Product_Family pf on pf.CC_Code=l.CC_Code
) x on i.EC_Code=x.EC_Code and i.Spread=x.Spread
left join Inter_CS_Delta d on d.EC_Code=x.EC_Code and d.Spread=x.Spread
left join Inter_CS_Scan s on s.EC_Code=x.EC_Code and s.Spread=x.Spread
left join Inter_Tier p on p.EC_Code=x.EC_Code and p.CC_Code=x.CC_Code AND p.Tier_Number=i.Inter_Tier_Number
left join Market m on m.ID=x.mkID

v_Intra
select l.EC_Code as ec,l.CC_Code as cc,l.Intra_Spread as spread,l.Relative_Side as side,l.Ratio as ratio,s.Rate_Value as rateValue,l.Period as startDate,l.Period as endDate,pf.Currency_Code as currency,pf.PF_Code as pfcode,m.CName as MkName from Intra_Period_Leg as l left join Intra_Commodity_Spread as s on l.EC_Code=s.EC_Code and l.CC_Code=s.CC_Code and l.Intra_Spread=s.Spread INNER JOIN Product_Family as pf on pf.EC_Code=s.EC_Code and pf.CC_Code=s.CC_Code INNER JOIN Market m on m.ID=pf.Market_ID
union
select l.EC_Code as ec,l.CC_Code as cc,l.Intra_Spread as spread,l.Relative_Side as side,l.Ratio as ratio,s.Rate_Value as rateValue,t.Start_Period as startDate, t.End_Period as endDate,pf.Currency_Code as currency,pf.PF_Code as pfcode,m.CName as MkName from Intra_Tier_Leg as l left join Intra_Tier as t on l.EC_Code=t.EC_Code and l.CC_Code=t.CC_Code and l.Tier_Number=t.Tier_Number left join Intra_Commodity_Spread as s on l.EC_Code=s.EC_Code and l.CC_Code=s.CC_Code and l.Intra_Spread=s.Spread INNER JOIN Product_Family as pf on pf.EC_Code=s.EC_Code and pf.CC_Code=s.CC_Code INNER JOIN Market m on m.ID=pf.Market_ID

 

v_Tier
SELECT s.*,pf.Currency_Code as currency,pf.PF_Code as pfcode,m.CName as MkName FROM Som_Tier s INNER JOIN Product_Family as pf on pf.EC_Code=s.EC_Code and pf.CC_Code=s.CC_Code INNER JOIN Market m on m.ID=pf.Market_ID

 

转载于:https://www.cnblogs.com/xfcao/p/9438996.html