多條件運算時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