Id Account_name Date Debit Credit
1 revenue 2014-05-05 808001 555001
2 expense 2014-05-05 48000 90000
3 a/p 2014-05-05 800 1000
3 a/r 2014-05-05 36000 50000
4 rent_revenue 2014-05-05 40000 50000
好，这是我的表，我想计算总收入的总余额，a/r, sql查询如下给定
SELECT account_name, SUM(debit)+SUM(credit) AS TOTAL BALANCE
WHERE account_name LIKE '%revenue%'
问题是它只显示了account_name='revenue'的结果和它的总余额，我也想要另一行，我该怎么办?
我想你在找这样的东西:
SELECT account_name ,SUM(debit)+SUM(credit) AS `TOTAL BALANCE`
FROM Tablename
WHERE account_name LIKE '%revenue%'
GROUP BY account_name
如果您希望每个帐户的借方和贷方的总和返回，您可以使用
-- sum of debit and credit for each group of accounts that share the same name and match the pattern
SELECT account_name,
SUM(debit) + SUM(credit) AS TOTAL_BALANCE
FROM ACCOUNTS
WHERE account_name LIKE '%revenue%'
GROUP BY account_name
或简称
-- sum of debit and credit for each account that matches the pattern
SELECT account_name,
debit + credit AS TOTAL_BALANCE
FROM ACCOUNTS
WHERE account_name LIKE '%revenue%'
，它可以在没有组的情况下运行。
如果您希望在结果集中返回匹配模式的所有帐户的总和和模式，则必须以某种方式将您的模式包含在group by子句中。例如
-- returns the respective sums over all debits and credits with the same last 7 signs in their names, as far as the match the pattern in the where clause
SELECT RIGHT(Account_Name, 7) acc_name_tr,
SUM(debit) + SUM(credit) AS TOTAL_BALANCE
FROM Accounts23525506
WHERE Account_Name LIKE '%revenue%'
GROUP BY RIGHT(Account_Name, 7)
最后一条语句应该产生
acc_name_tr TOTAL_BALANCE
revenue 1453002
但是我把你的模式改成了" account_name以'revenue'结尾"，所以要注意group-by-pattern是否仍然符合你的目的。
试试这个:
SELECT account_name , debit+credit AS TOTAL_BALANCE WHERE account_name LIKE '%revenue%'
或者，试试这个…
SELECT account_name
,SUM(debit)+SUM(credit) AS `TOTAL BALANCE`
WHERE
Right(account_name,7) = 'revenue'
GROUP BY account_name
在account_name上使用Group By，然后使用having子句对输出进行限制。
SELECT account_name, SUM(debit+credit) AS [TOTAL BALANCE]
FROM Tablename
GROUP BY account_name
HAVING account_name LIKE '%revenue%' .