MySQL行小计和总计的子合计

Question

MySQL行小计和总计的子合计

3

我需要创建一个MariaDB SQL语句，可以对两列（借方和贷方）求和得到差额，并返回每个不同的账户类型的小计。表格如下：

 Account     | Debit  | Credit
 acc1        |   1    |   2 
 acc1        |   1    |   4
 acc2        |   3    |   2
 acc2        |   2    |   1
 acc2        |   2    |   1
 acc3        |   5    |   2
 acc3        |   5    |   1
 acc3        |   5    |   2

我希望返回以下内容：

 Account     | Balance(debit-credit)
 acc1        |   -1    
 acc1        |   -3 
 -------------------------------
 Total acc1  |   -4 
 -------------------------------
 acc2        |   1
 acc2        |   1
 acc2        |   1
 -------------------------------
 Total acc2  |   3 
 -------------------------------
 acc3        |   3
 acc3        |   4
 acc3        |   3
 -------------------------------
 Total acc3  |   10 
 -------------------------------
 GrandTotal  |   9 
 -------------------------------

 Grandtotal is Totals of acc1 + acc2 + acc3

这是我目前尝试过的，但是我只得到了总计而没有小计。

SELECT * FROM (
 SELECT COALESCE(account,'TOTAL') AS Account, CASE
        WHEN account LIKE 'INC%' 
            THEN sum((gl.credit - gl.debit))
        ELSE sum((gl.debit - gl.credit))
    END AS Balance
 FROM `tabGL Entry` gl
 WHERE (NOT (account LIKE 'CASS%')
     AND NOT (account LIKE 'CLIA%')
     AND NOT (account LIKE 'FASS%'))
 GROUP BY account WITH ROLLUP
) AS gl
 ORDER BY CASE
     WHEN account LIKE 'INC%' THEN 1
     WHEN account LIKE 'DCOI%' THEN 2
     WHEN account LIKE 'DMC%' THEN 3
     WHEN account LIKE 'INFC%' THEN 4
     WHEN account LIKE 'IDEX%' THEN 5
     ELSE 6
 END

- Said

我实在想象不出会有同时存在借方和贷方相邻的列的情况。不过，你忘了加入 PRIMARY KEY 这一关键字段，它的缺失使得这个问题基本上无法解决。 - Strawberry

恐怕这个问题有些牵强。如果你没有主键，那么你实际上没有表。如果你没有真正的表，那么这就不是一个真正的关系型数据库管理系统问题。 - Strawberry

@Strawberry，实际上我在所有方面都做到了，并且关于主键它确实有。我之所以询问一个全新的SQL语句是因为现有的语句显然没有返回所需的结果。这就是为什么我问是否有另一种方法来处理我提到的语句。它绝对不是虚构的。 - Said

没问题，只是回复你之前的评论。 :) - Said

我不知道你所说的“本质上不是关键字”是什么意思。如果你有一个主键，请与我们分享。主键可以是在多列组合上形成的自然键。它不一定是代理键（虽然如果是代理键，问题会稍微简单一些）。哦，还有请参见：为什么我需要为一个看起来非常简单的SQL查询提供MCVE？ - Strawberry

显示剩余3条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- RealCheeseLord · Accepted Answer

您可以尝试使用UNION，例如：

。

SELECT * 
  FROM (SELECT COALESCE('TOTAL ', account) AS Account
               , SUM(CASE WHEN account LIKE 'INC%' 
                          THEN (gl.credit - gl.debit)
                          ELSE (gl.debit - gl.credit)
                      END) AS Balance
          FROM `tabGL Entry` gl
         WHERE (NOT (account LIKE 'CASS%')
           AND NOT (account LIKE 'CLIA%')
           AND NOT (account LIKE 'FASS%'))
         GROUP BY account WITH ROLLUP
  -- UNION SELECT account
  --              , CASE WHEN account LIKE 'INC%' 
  --                     THEN (gl.credit - gl.debit)
  --                     ELSE (gl.debit - gl.credit)
  --                 END AS Balance
  --         FROM `tabGL Entry` gl
  --        WHERE (NOT (account LIKE 'CASS%')
  --          AND NOT (account LIKE 'CLIA%')
  --          AND NOT (account LIKE 'FASS%'))
) AS gl
 ORDER BY CASE
 WHEN account LIKE 'INC%' THEN 1
 WHEN account LIKE 'DCOI%' THEN 2
 WHEN account LIKE 'DMC%' THEN 3
 WHEN account LIKE 'INFC%' THEN 4
 WHEN account LIKE 'IDEX%' THEN 5
 ELSE 6
END

我认为这应该能满足你的需求。

注释部分是针对单行的，上部分是针对小计的。

现在，在这里最后的注释之后，查询将为您提供小计和总计。

通过COALESCE进行分组似乎是问题所在，因此现在它通过LEFT（account，3）完成，并且ORDER语句必须修改为3个字符。

SELECT * 
  FROM (SELECT LEFT(account,3) AS Account
           , SUM(CASE WHEN account LIKE 'INC%' 
                      THEN (credit - debit)
                      ELSE (debit - credit)
                  END) AS Balance
          FROM acc
         GROUP BY LEFT(account,3) WITH ROLLUP
) AS ac
 ORDER BY CASE
 WHEN account LIKE 'INC%' THEN 1
 WHEN account LIKE 'DCO%' THEN 2
 WHEN account LIKE 'DMC%' THEN 3
 WHEN account LIKE 'INF%' THEN 4
 WHEN account LIKE 'IDE%' THEN 5
 ELSE 6
END