select b.姓名,a.rcpt_no as 收据,
sum(case when a.money_type='卡支付' then income else 0 end) as 卡支付 ,
sum(case when a.money_type='统筹支付'then income else 0 end) as 统筹支付,
sum(case when a.money_type='现金'then income else 0 end) as 支付现金,
sum(case when a.money_type='现金'then refund else 0 end) as 退还现金,
sum(case when a.money_type='现金'then income-refund else 0 end) as 实际支付现金,
sum(case when a.money_type='记账'then income-refund else 0 end) as 记账,
sum(income-refund) as 总费用
/*sum(income) as 卡支付*/
from inp_payments_money a,pat_master_index b
where a.rcpt_no=b.rcpt_no
group by a.rcpt_no,b.姓名
order by a.rcpt_no,b.姓名;
返回:
现在要新增一行,以求每列的和,
哦,完全正确,现在我自己用的是SQL Server,但要在Oracle上实现呢,你也给我写写吧。
合计不在最后一列了,呵呵,你再看看吧,期待你的答案。
呵呵,报with附近有错,再帮我改改吧