作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个非常缓慢的搜索,它通过查找主要记录然后在一个线程中循环以对相关交易求和来完成。我试图让它在一个报表中工作,已经接近但仍然有交替借方和贷方的记录。
我无法弄清楚如何将借方行和贷方行向上拉到列中,以便每个日期和工作键有一行结果y。
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;
关于行中的 SQL 求和被提取到列中?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/578688/
我是一名优秀的程序员,十分优秀!