来自同一表的不同日期的相同数据

Question

我在甲骨文中有一张表格，我想得到sysdate的借方和信贷栏的差额，以及6个月前借方和信贷栏的差额。

我的疑问是

select a.name,a.id, nvl(sum(a.debit),0)-nvl(sum(a.credit),0) current_bal
from mytable a
where a.id='1092' and a.docdate<=sysdate
group by a.name,a.id
union
select b.name,b.id,nvl(sum(b.debit),0)-nvl(sum(b.credit),0) current_bal1
from mytable b
where b. id='1092' and b.docdate<=add_months(sysdate,-6)
group by b.name,b.id;

我得到了正确的结果，但是查询返回了两行，因为我需要将结果显示为一行。

如有任何建议或更正，请在我的查询。

Answer 1

您可以按以下方式使用条件聚合：

select a.name,a.id, nvl(sum(CASE WHEN a.docdate<=sysdate THEN a.debit END),0)-nvl(sum(CASE WHEN a.docdate<=sysdate THEN a.credit END),0) current_bal,
nvl(sum(CASE WHEN b.docdate<=add_months(sysdate,-6) THEN a.debit END),0)-nvl(sum(CASE WHEN b.docdate<=add_months(sysdate,-6) THEN a.credit END),0) current_bal1
from mytable a
where a.id='1092'
group by a.name,a.id;

--更新

如果您面临任何问题，那么最简单的方法是使用子查询之间的自联接，如下所示：

SELECT A.NAME, A.ID, A.CURRENT_BAL, B.CURRENT_BAL1
FROM
(select a.name,a.id, nvl(sum(a.debit),0)-nvl(sum(a.credit),0) current_bal
from mytable a
where a.id='1092' and a.docdate<=sysdate
group by a.name,a.id) A
JOIN 
(select b.name,b.id,nvl(sum(b.debit),0)-nvl(sum(b.credit),0) current_bal1
from mytable b
where b. id='1092' and b.docdate<=add_months(sysdate,-6)
group by b.name,b.id) B
ON A.ID = B.ID AND A.NAME = B.NAME;

Answer 2

您可以使用条件聚合：

select a.name, a.id, 
       coalesce(sum(a.debit), 0) - coalesce(sum(a.credit), 0) as current_bal,
       (sum(case when a.docdate < add_months(sysdate, -6) then a.debit else 0 end) -
        sum(case when a.docdate < add_months(sysdate, -6) then a.credit else 0 end)
       ) as bal_6_months
from mytable a
where a.id = '1092' and a.docdate <= sysdate
group by a.name, a.id;

这将两个值放在同一行中。这对我来说似乎更有用，然后把它们放在不同的行中。

Answer 3

你能试试：

select a.name,a.id, LISTAGG(nvl(sum(a.debit),0)-nvl(sum(a.credit),0), ' ') WITHIN GROUP (ORDER BY a.id) current_bal
from mytable a
where a.id='1092' and a.docdate<=sysdate
group by a.name,a.id
union
select b.name,b.id, LISTAGG(nvl(sum(a.debit),0)-nvl(sum(a.credit),0), ' ') WITHIN GROUP (ORDER BY a.id) current_bal
from mytable b
where b. id='1092' and b.docdate<=add_months(sysdate,-6)
group by b.name,b.id;