MySQL 行的小计与总计
我需要创建一个 MariaDB SQL,它允许我对两列(借方和贷方列)求和以获得差异,并返回每种不同账户类型的小计.表格如下:
I need to create a MariaDB SQL that will allow me to sum two columns (Debit & Credit Columns) to get the difference, as well as return the subtotal of every different account type. The table is as follows:
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
这是我到目前为止所尝试的,但我只得到总计而没有小计
This is what I have tried so far, how ever all I am only getting the grandtotal and no subtotals
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
推荐答案
你可以试试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
我认为这应该做你想做的事
I think this should do what you want
注释掉的部分是单行,上半部分是小计
The commented-out part is for the single rows, the upper part for subtotals
现在在此处的最后一条评论之后,为您提供小计和总计的查询
Now after the last comment here the query that gives you the subtotals and the grandtotal
按 COALESCE 分组似乎是个问题,所以现在由 LEFT(account,3)
完成,ORDER
语句必须修改为 3 个字符
grouping by COALESCE seemed to be the Problem so now it's done by LEFT(account,3)
and the ORDER
Statement had to be modified to 3 Chars
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
相关文章