我有两张桌子,一张是表F,一张是表D。
表F给出了帐户的财务交易(付款、通行费、利息)的详细信息。表D显示欠款状态(如果用户尚未支付到帐户中)。它显示不同的状态代码。0,1,2,11 (这意味着不同的东西)。
我的表结构如下:
Table F
============
-------------------------------------------
|AccountId|TypeCode |BusinessDate|TransAmt|
-------------------------------------------
|12345 | PYMT |2016-06-22 | 10.54|
-------------------------------------------
|12345 | TOLL |2016-06-15 | 04.00|
-------------------------------------------
|12345 | INTR |2016-05-26 | 01.66|
-------------------------------------------
|12345 | TOLL |2016-04-10 | 04.00|
-------------------------------------------
Table D
==========
-----------------------------------------------
|AccountId|FromID|ToID|CreatedDate |
-----------------------------------------------
|12345 | 0| 2|2016-12-25 00:12:44.453|
-----------------------------------------------
|12345 | 2| 1|2017-04-02 07:16:46.770|
-----------------------------------------------
|12345 | 1| 11|2017-07-12 16:16:36.747|
-----------------------------------------------
我正在尝试运行代码来查找在帐户进入状态代码11 (ToID)后在该帐户上进行的所有付款的总和。
我的代码:
SELECT F.AccounttID
,ISNULL(SUM(TransAmt), 0) Payments
FROM F INNER JOIN D ON F.AccounttID = D.AccountId
WHERE AccountId = 12345
AND TypeCode = 'PYMT'
AND F.BusinessDate >= D.CreatedDate
AND ToID = 11
--AND F.BusinessDate >= (SELECT CreatedDate FROM D WHERE F.AccounttID = D.AccountId AND ToID = 11)
GROUP BY F.AccountID, F.TypeCode
我得到的结果是:
--------------------
|AccountID|Payments|
--------------------
| | |
--------------------
我想要的结果是:
--------------------
|AccountID|Payments|
--------------------
| 12345| 0.00|
--------------------
发布于 2018-06-08 04:55:23
您的问题是没有与您的where
标准匹配的结果。我认为您实际上是在考虑使用conditional aggregation
。我还使用了outer join
,以防没有匹配:
SELECT F.AccounttID
,SUM(CASE WHEN F.BusinessDate >= D.CreatedDate THEN F.TransAmt ELSE 0 END) Payments
FROM F
LEFT JOIN D ON F.AccounttID = D.AccountId AND D.ToId = 11
WHERE F.AccountId = 12345
AND F.TypeCode = 'PYMT'
GROUP BY F.AccountID
发布于 2018-06-08 04:47:34
删除GROUP BY
SELECT MAX(F.AccounttID) as AccounttID,
COALESCE(SUM(TransAmt), 0) Payments
FROM F INNER JOIN
D
ON F.AccounttID = D.AccountId
WHERE AccountId = 12345 AND
TypeCode = 'PYMT' AND
F.BusinessDate >= D.CreatedDate AND
ToID = 11
--AND F.BusinessDate >= (SELECT CreatedDate FROM D WHERE F.AccounttID = D.AccountId AND ToID = 11)
这似乎有悖于常理。但是,没有GROUP BY
的聚合查询保证只返回一行,即使所有行都被过滤掉了。使用GROUP BY
,聚合查询将为每个组返回一行。如果所有行都被过滤掉,则不返回任何行。
https://stackoverflow.com/questions/50749808
复制相似问题