多条件运算时SQL的写法

当需要基于不同条件产生的结果集上运算时，我们可以采用对单一结果集运算后，保持原有结果集，然后统一根据某个字段聚合后来求一些字段的和。

select A.port_no as port_no,

(select max(key_date) from dbo.TblHNWDate) as key_date,
sum(A.pfd_shares) as tot_pfd_shares,
sum(A.par_value) as tot_par_value,
sum(A.cost) as tot_cost,
sum(A.adjusted_cost) as tot_adjusted_cost,
sum(A.market) as tot_market_value,
CASE
when (sum(A.market)-sum(A.adjusted_cost))>0 then ''
else 'N'
end
as tot_net_unrl_gl_s,
(sum(A.market)-sum(A.adjusted_cost)) as tot_net_unrl_gl
from
(
select
port.port_no as port_no,
case
when port.mod_secr_type='05' AND secr.class_code!='934' then port.quantity
else 0
end
as pfd_shares,
case
when port.mod_secr_type='05' AND secr.class_code!='934' then (port.quantity*secr.unit_cash_value)
when port.mod_secr_type='05' AND secr.class_code='934' then (port.quantity*secr.market_price_nuv)
when port.mod_secr_type!='05' then port.quantity
end
as par_value,
port.cost as cost,
port.adjusted_cost as adjusted_cost,
port.market as market
from TblHNWPort port,TblHNWSecr secr
where port.cusip = secr.cusip
and port.key_date = secr.key_date)A
group by A.port_no