sql - 在 SQL 中计算插头/余额

Question

我当前的表中有一些帐户在当前表中始终具有最小值（例如，X 和 Y 用于实际，AA、BB、CC 和 DD 用于计划）。具有最小值的帐户可以超出这些帐户。我需要将此类帐户分类为称为“余额”的代理帐户，该帐户始终为 [final-(abc+def+ghi)]（请参阅预期表）。我正在尝试在 SQL 中使用 sum (case when account not in ('abc','def','ghi').

此处的示例数据集：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)))
)

标签： sqlsql-servertsql

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