我有 2 个表 CustomerInfo 和 CustomerTransaction
客户信息
Name CustID Spouse Address TransactionID
Noah 11 Michael Wilson 72 Oxford Street 1234567
Kim 8 Ed Thumpson 67 York Street 7245678
Viktor 3 Brian Carter 16 Darlinghurst Street 9873567
James 9 Helen Wright 59 Paul Street 2135678
Liam 5 Karen Collins 82 Newtown Park 2356789
Sandra 6 Karen Collins 82 Newtown Park 2137849
客户交易
TransactionID LastTransactionTime Type
2356789 8/01/19 11:50 Credit
2137849 7/21/19 09:30 Credit
1234567 8/02/19 11:50 Money
7245678 7/25/19 11:50 Credit
9873567 7/17/19 11:50 Credit
9873567 7/22/19 01:50 Credit
2137849 7/29/19 09:50 Credit
9873567 4/10/19 23:50 Credit
2137849 7/13/19 16:50 Credit
7245678 5/1/19 19:50 Credit
需要编写一个查询,我必须在其中创建一个具有以下字段(事务计数(的新表。它应该只显示从上一个交易时间开始最近 30 天的计数事务计数。并且类型应该只是信用。
结果应该是:
CustID Type TransactionID LastTransactionTime TransactionCount
5 Credit 2356789 8/01/19 11:50 1
6 Credit 2137849 7/29/19 09:50 3
8 Credit 7245678 7/25/19 11:50 1
3 Credit 9873567 7/22/19 01:50 2
对于过去 30 天内发生的交易以及仅涉及信用的交易,请尝试按客户和交易进行聚合。
SELECT
ci.CustID,
'Credit' AS Type,
ci.TransactionID,
MAX(ct.LastTransactionTime) AS LastTransactionTime,
COUNT(*) AS TransactionCount
FROM CustomerInfo ci
INNER JOIN CustomerTransaction ct
ON ci.TransactionID = ct.TransactionID
WHERE
ct.LastTransactionTime > DATE_SUB(NOW(), 30) AND
ct.Type = 'Credit'
GROUP BY
ci.CustID,
ci.TransactionID;