SQl Server 常用查询

1. 多表查询：

select S.Symbol, S.SecType,PP.*, P.portid from moxyposition as pp, moxyportfolio as  p, Mxsec.secmaster as S where PP.PortKey = P.PortKey and PP.seckey = S.seckey and P.portid= 'port203'

select S.Symbol, S.SecType,PP.PreAllocPos, PP.AllocPos, PP.OpenPos, P.portid from moxyposition as pp, moxyportfolio as p, Mxsec.secmaster as S where PP.PortKey = P.PortKey and PP.seckey = S.seckey and P.portid= 'port203'

Join :

select P.portid, AA.Symbol, AA.sectype, AA.PreallocPos, AA.AllocPos, AA.OpenPos from moxyportfolio as p join (select pp.*, S.sectype, S.symbol from moxyposition as pp join Mxsec.secmaster as S on pp.seckey = S.seckey) AA on P.portkey = AA.PortKey where P.portID = 'port203'

 

2. 变量赋值查询

declare @userID int
select @userID = UserID from xr.vuser where ShortName = 'allc1'
select @userID

 

3.分组聚合查询

select count( *) as Result, rulecheckID from xr.rulecheckresult group by rulecheckID order by RulecheckID

select count(*) , ruleID, rulecheckID from xr.rulecheckresult group by ruleid, rulecheckID

select count(Resultid) as r, ruleid from xr.rulecheckresult group by ruleid having count(Resultid)>10 而不能用：select count(Resultid) as r, ruleid from xr.rulecheckresult group by ruleid having r>10

select count(Resultid) as r, ruleid from xr.rulecheckresult group by ruleid having ruleid >10

 

4. 常用函数查询

select Top 1 rulecheckID from xr.rulecheck order by rulecheckid desc
select Max(rulecheckID) from xr.rulecheck
select Min(rulecheckID) from xr.rulecheck
select sum(rulecheckid) from xr.rulecheck
select count(*) from xr.rulecheck
select avg(OpenPos) as avg_OpenPos, portkey from Moxyposition where seckey = 34340 group by portkey