sql - 行中的SQL总和被拉出到列中？

我有一个非常缓慢的搜索，通过找到主要记录，然后在一个线程中循环，以总结相关交易。我试图让它在一个单一的报表，已经接近，但仍然有记录，是交替借记和贷记。
我不知道如何将借方和贷方行拉到列中，所以每个日期和jobkey都有一行结果。

SELECT j.dtmInvoicedOn, j.strJobKey, c.strCustName, strTransType,
     SUM(r.dblTransactionAmount) AS SUM_dblTotalCharge
     FROM tbljobs AS j
     INNER JOIN tblreceivledger AS r ON j.strJobKey = r.strJobKey
     INNER JOIN tblcustomers AS c ON j.intCustomerID = c.intCustomerID
     WHERE c.strCustomerName = 'Acme Runners Inc'
     GROUP BY j.strJobKey, c.strCustName, strTransType
     ORDER BY dtmInvoicedOn, strJobKey;

产生这样的输出，借贷金额几乎是交替的

+----------------+---------------+------------------+--------------------+--------------------+
| dtmInvoicedOn  | strJobKey     | strCustomerName  | strTransactionType | SUM_dblTotalCharge |
+----------------+---------------+------------------+--------------------+--------------------+
| 2008-07-03     | 270876-1      | Acme Runners Inc | credit             |           -5531.52 |
| 2008-07-11     | 270880-1      | Acme Runners Inc | debit              |            5058.54 |
| 2008-07-11     | 270880-1      | Acme Runners Inc | credit             |           -5058.54 |
| 2008-07-18     | 271468-1      | Acme Runners Inc | debit              |            5290.17 |
| 2008-07-18     | 271468-1      | Acme Runners Inc | credit             |           -5290.17 |
| 2008-11-07     | 286049-1      | Acme Runners Inc | debit              |            5230.44 |
| 2008-11-14     | 286051-1      | Acme Runners Inc | debit              |            5375.14 |
| 2008-11-21     | 286107-1      | Acme Runners Inc | debit              |            5572.33 |
| 2008-11-28     | 286112-1      | Acme Runners Inc | debit              |            5123.42 |

所以我希望它看起来像：

+----------------+---------------+------------------+----------+----------+
| dtmInvoicedOn  | strJobKey     | strCustomerName  |   credit |    debit |
+----------------+---------------+------------------+----------+----------+
| 2008-07-03     | 270876-1      | Acme Runners Inc | -5531.52 |        0 |
| 2008-07-11     | 270880-1      | Acme Runners Inc | -5058.54 |  5058.54 |
| 2008-07-18     | 271468-1      | Acme Runners Inc | -5290.17 |  5290.17 |
| 2008-11-07     | 286049-1      | Acme Runners Inc |        0 |  5230.44 |
| 2008-11-14     | 286051-1      | Acme Runners Inc |        0 |  5375.14 |
| 2008-11-21     | 286107-1      | Acme Runners Inc |        0 |  5572.33 |
| 2008-11-28     | 286112-1      | Acme Runners Inc          0 |  5123.42 |

请注意，服务器当前正在运行mysql，但稍后将迁移到postgres和sqlite。
谢谢

最佳答案

这应该可以做到：

SELECT j.dtmInvoicedOn, j.strJobKey, c.strCustName, strTransType,
     SUM(CASE WHEN strTransType='credit' THEN r.dblTransactionAmount ELSE 0 END) AS SUM_CREDIT,
     SUM(CASE WHEN strTransType='debit' THEN r.dblTransactionAmount ELSE 0 END) AS SUM_DEBIT
FROM tbljobs AS j
     INNER JOIN tblreceivledger AS r ON j.strJobKey = r.strJobKey
     INNER JOIN tblcustomers AS c ON j.intCustomerID = c.intCustomerID
WHERE c.strCustomerName = 'Acme Runners Inc'
GROUP BY j.strJobKey, c.strCustName
ORDER BY dtmInvoicedOn, strJobKey;