sql server和联合

Question

select '' as xxx,pawn.propawn_price as yyy from product_pawn pawn
inner join product_history history on history.propawn_id = pawn.propawn_id
and history.prohistory_status = '0'
union 
select pawn.propawn_price * 8/100 as xxx,'' as yyy from product_pawn pawn
inner join product_history history on history.propawn_id = pawn.propawn_id
and history.prohistory_status = '1'
union
select pawn.propawn_price + pawn.propawn_price * 8/100 as xxx,'' as yyy 
from product_pawn pawn
inner join product_history history on history.propawn_id = pawn.propawn_id
and history.prohistory_status = '2'
union
select prosell_pricesell as xxx,'' as yyy from product_sell
where prosell_status = '5'

结果

xxx yyy

0- 900

0- 800

700 -0

0- 100

500 -0

我想要sum xxx和sum yyy

1200 1800

Answer 1

你可以试着这样做。

  SELECT Sum(xxx) as xxx,Sum(yyy) as yyy from (
    select '' as xxx,pawn.propawn_price as yyy from product_pawn pawn
    inner join product_history history on history.propawn_id = pawn.propawn_id
    and history.prohistory_status = '0'
    union 
    select pawn.propawn_price * 8/100 as xxx,'' as yyy from product_pawn pawn
    inner join product_history history on history.propawn_id = pawn.propawn_id
    and history.prohistory_status = '1'
    union
    select pawn.propawn_price + pawn.propawn_price * 8/100 as xxx,'' as yyy 
    from product_pawn pawn
    inner join product_history history on history.propawn_id = pawn.propawn_id
    and history.prohistory_status = '2'
    union
    select prosell_pricesell as xxx,'' as yyy from product_sell
    where prosell_status = '5') t