Question

sql语句实现增加一行求和

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.姓名;
返回：

现在要新增一行，以求每列的和，

Answer 1

如果是SQL Server, 尝试用下面的语句

select
case when GROUPING(a.rcpt_no) = 1 and GROUPING(b.姓名) = 1 then 'SUM' else Max(b.姓名) end 姓名,
case when GROUPING(a.rcpt_no) = 1 and GROUPING(b.姓名) = 1 then 'SUM' else Max(a.rcpt_no) end 收据,
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.姓名 with rollup
having GROUPING(a.rcpt_no) = 1 and GROUPING(b.姓名) = 1 or GROUPING(a.rcpt_no) = 0 and GROUPING(b.姓名) = 0
order by Max(a.rcpt_no),Max(b.姓名)

追问

哦，完全正确，现在我自己用的是SQL Server,但要在Oracle上实现呢，你也给我写写吧。

Answer 2

你有sum子句中怎么用到三个'现金'，是不是有误呀，如果正确，试试下面
SELECT *
FROM (
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.姓名
)
t

UNION ALL
SELECT
'合计',
'',
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 总费用
from inp_payments_money a
order by t.收据,t.姓名

追问

合计不在最后一列了，呵呵，你再看看吧，期待你的答案。

Answer 3

WITH TMP_TAB (姓名, 收据, 卡支付, 统筹支付, 支付现金, 退还现金, 实际支付现金, 记账, 总费用) AS
(
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 总费用
from inp_payments_money a,pat_master_index b
where a.rcpt_no=b.rcpt_no
group by a.rcpt_no,b.姓名
)
SELECT 姓名, 收据, 卡支付, 统筹支付, 支付现金, 退还现金, 实际支付现金, 记账, 总费用 FROM TMP_TAB
UNION ALL
SELECT '合计', '--', SUM(卡支付), SUM(统筹支付), SUM(支付现金), SUM(退还现金), SUM(实际支付现金), SUM(记账), SUM(总费用) FROM TMP_TAB
ORDER BY 1,2;

追问

呵呵，报with附近有错，再帮我改改吧