在SQL中计算plug/balance

Question

我的当前表有几个帐户，它们在当前表中总是具有最小值(例如，实际的X&Y和计划的AA、BB、CC和DD )。具有最小值的帐户可以扩展到这些帐户之外。我需要将这些帐户归入名为"balance“的代理帐户中，该帐户将始终为

最终-(abc+def+ghi)

..。我正在尝试在SQL中使用以下命令来构造它，这是很困难的

..。

​

​

示例数据集如下：

http://sqlfiddle.com/#!9/114cfe/1

SQL尝试

select year, month, type,

case when accounts in 'abc' then 'abc'
case when accounts in 'def' then 'def'
case when accounts in 'ghi' then 'ghi'
case when accounts in 'final' then 'final'
else 'balance'
end as account_2
,
(
(case when accounts in ('abc','def','ghi','final') then sum(amount)
else 
(
(case when accounts in ('final') then sum(amount))-
sum(case when accounts in ('abc','def','ghi') then sum(amount) else 0)))
)

Answer 1

我认为将它分成两个步骤会更容易。在一个步骤中更改值，并在另一个步骤中对行进行分组。

SELECT year, month, type, account_2, SUM(amount) AS amount_2
FROM (
    SELECT year, month, type, amount,
        CASE WHEN accounts IN ('abc', 'def', 'ghi', 'final') THEN accounts 
        ELSE 'balance' END AS account_2
    FROM someTable
) AS tmp
GROUP BY year, month, type, account_2