减借贷方栏目

Question

我用sql写了下面的视图

SELECT     TOP (100) PERCENT Accounting.TopicA.CodeA, SUM(Accounting.DocumentDetail.Debit) AS DEB, SUM(Accounting.DocumentDetail.Credit) AS CRED, 
                      Accounting.TopicA.Description
FROM         Accounting.TopicA INNER JOIN
                      Accounting.DocumentDetail ON Accounting.TopicA.CodeA = SUBSTRING(Accounting.DocumentDetail.Topic, 1, 2)
GROUP BY Accounting.TopicA.CodeA, Accounting.TopicA.Description
ORDER BY Accounting.TopicA.CodeA

结果就是

codea |Description | DEB | CRED |
1      Bank        | 100 | 30   |
2      Cash        | 40  | 70   |
.
.
.

现在我需要添加另外两个列来减去DEB和CRED，比如当减法为正时，然后将结果放入POS列，否则将结果放入NEG列，如下所示

codea |Description | DEB | CRED |  NEG | POS |
1      Bank        | 100 | 30   | 70   | 0   |
2      Cash        | 40  | 70   | 0    | 30  |
.
.
.

Answer 1

尝试如下所示：

SELECT TOP (100) PERCENT Accounting.TopicA.CodeA,
SUM(Accounting.DocumentDetail.Debit) AS DEB,
SUM(Accounting.DocumentDetail.Credit) AS CRED,
CASE WHEN SUM(Accounting.DocumentDetail.Credit) - SUM(Accounting.DocumentDetail.Debit) < 0
THEN SUM(Accounting.DocumentDetail.Debit) - SUM(Accounting.DocumentDetail.Credit)
ELSE 0 END AS NEG,
CASE WHEN SUM(Accounting.DocumentDetail.Credit) - SUM(Accounting.DocumentDetail.Debit) > 0
THEN SUM(Accounting.DocumentDetail.Credit) - SUM(Accounting.DocumentDetail.Debit)
ELSE 0 AS POS,
Accounting.TopicA.Description
FROM Accounting.TopicA 
INNER JOIN Accounting.DocumentDetail 
ON Accounting.TopicA.CodeA = SUBSTRING(Accounting.DocumentDetail.Topic, 1, 2)
GROUP BY Accounting.TopicA.CodeA, Accounting.TopicA.Description
ORDER BY Accounting.TopicA.CodeA

Answer 2

我的想法是使用一个CASE语句来做这件事。

以下是指向sql server案例文档的链接：http://msdn.microsoft.com/en-us/library/ms181765.aspx

我只是猜测您使用的是sql server，因为问题被标记为C#，尽管它应该与mysql，oracle，...也是。

其思想是使用这种情况来知道何时将0放入NEG或POS列，以及将实际结果放在何处。

注意:您真的需要NEG和POS列吗？您不是只想创建一个"result“列吗？

Answer 3

对于负数列，您可以使用：

ABS(Min(0, theSubtraction))

对于正数列，您可以使用：

Max(0, theSubtraction)