如何将金额、借方、贷方和余额排列在一行中

Question

我想将查询排列成单行(每个cdvno、金额、借方、贷方和余额1行(但是我不能安排他们。进入1个单行

样本代码

select a.cdvno,b.Amount, a.debit, a.credit, a.credit + a.debit - coalesce(b.amount,0) as balance
from (
select cdvno, debit, sum(credit) credit, trantype
from cdvdtl  
where debit = 0
group by cdvno, debit, trantype
union all
select cdvno, sum(debit) debit, credit, trantype
from cdvdtl  
where credit = 0
group by cdvno, credit, trantype
) a
left join cdvhdr b
on b.cdvno = a.cdvno
and b.trantype = a.trantype
where  a.credit + a.debit - coalesce(b.amount,0) <> 0
order by a.cdvno

结果

cdvno               Amount      debit     credit    balance
000-2016-01000004   25137.50    25326.16    0.00    188.66
000-2016-01000004   25137.50    0.00    25326.16    188.66
000-2016-01000005   15849.90    0.00    16010.00    160.10
000-2016-01000005   15849.90    16010.00    0.00    160.10
000-2016-01000007   217938.73   0.00    220006.50   2067.77
000-2016-01000007   217938.73   220006.50   0.00    2067.77

我试过(合计(金额，但不起作用。如果可能的话，我希望结果像这个

cdvno             Amount        debit       credit      balance
000-2016-01000004   25137.5     25326.16    25326.16    188.66
000-2016-01000005   15849.9     16010       16010       160.1
000-2016-01000007   217938.73   220006.5    220006.5    2067.77

非常感谢你们，我知道这对你们来说很简单，但对我来说，我被困在这里了：D新手

Answer 1

您可以忽略union(这导致为借方和credit创建单独的条目(，而使用select case。

select a.cdvno,b.Amount
, a.debit
, a.credit
, coalesce(b.amount,0) - coalesce(a.debit, a.credit) as balance
from (
select cdvno
, sum(case when credit = 0 then debit else 0 end) as debit
, sum(case when debit = 0 then credit else 0 end) credit
, trantype
from cdvdtl    
group by cdvno, trantype    
) a 
left join cdvhdr b
on b.cdvno = a.cdvno and b.trantype = a.trantype
where  a.credit + a.debit - coalesce(b.amount,0) <> 0
order by a.`cdvno`

Answer 2

我认为您想要聚合条件聚合。这对我来说是有意义的：

select d.cdvno,
sum(case when d.credit = 0 then d.debit end) as debit,
sum(case when d.debit = 0 then d.credit end) as credit,
(h.amount +
sum(case when d.debit = 0 then d.credit end) -
sum(case when d.credit = 0 then d.debit end)
) as balance
from cdvdtl d join
cdvhdr h
on h.cdvno = d.cdvno and h.trantype = d.trantype
group by d.cdvno, h.amount
order by d.cdvno;

请注意表别名的使用。这些不是任意字母，而是表名的缩写。