SQL 获取总和 > 的所有付款72 小时内 10,000 [英] SQL get all Payments where sum > 10,000 in 72 hours
问题描述
我正在开发一个虚构的支付数据库,我想检查是否有商家的付款在 72 小时内超过 10k.
i'm working on a fictional payment database, and i want to check if there are payments from a merchant which sum of it goes above 10k within 72 hours from each other.
SELECT o.MerchantID, SUM(Amount) FROM Payments p
INNER JOIN Orders o ON p.OrderID = o.ID
WHERE MerchantCreatedOrderOn BETWEEN MerchantCreatedOrderOn AND DATEADD(HOUR, 72, MerchantCreatedOrderOn)
GROUP BY o.MerchantID, o.MerchantCreatedOrderOn, p.ID
HAVING SUM(o.Amount) >= 10000;
现在我的 where 条款还不正确,我现在只收到金额超过 10k 的付款,但不会在 72 小时内收到付款..
now my where clause isn't right yet, i now get only payments where the sum is above 10k but not when it's in 72 hours..
我想要的结果:72 小时内付款总额 > 10,000 的所有付款 ID.
my desired result: all the payments id's from payments which sum > 10,000 in 72 hours.
这是在 SQL-server 14.0.100 上
this is on SQL-server 14.0.100
我只需要 72 小时内超过 10k 的付款 ID所以:
i only need the payment id's from those who exceed 10k in 72 hours so:
|ID |
|427683|
|427685|
|427688|
推荐答案
根据我的评论,我假设您想检查自订单日期起 72 小时内的每个订单.该公式实际上从日期顺序开始 -72 小时
From my comment, I assume you want to check every orders from 72 hours from date order. The formula actually -72 hours from date order
SELECT o.MerchantID, SUM(Amount) FROM Payments p
INNER JOIN Orders o ON p.OrderID = o.ID
WHERE MerchantCreatedOrderOn BETWEEN MerchantCreatedOrderOn AND DATEADD(HOUR, -72, MerchantCreatedOrderOn)
GROUP BY o.MerchantID, o.MerchantCreatedOrderOn, p.ID
HAVING SUM(o.Amount) >= 10000;
我只是将 72 更改为 -72.
I just changing 72 become -72.
但如果我理解正确的话,实际上您现在想要 72 小时,像这样?
But if I get you correctly, actually you want 72 hours by now, which like this?
SELECT o.MerchantID, SUM(Amount) FROM Payments p
INNER JOIN Orders o ON p.OrderID = o.ID
WHERE MerchantCreatedOrderOn >= DATEADD(HOUR, -72, getdate())
GROUP BY o.MerchantID, o.MerchantCreatedOrderOn, p.ID
HAVING SUM(o.Amount) >= 10000;
这篇关于SQL 获取总和 > 的所有付款72 小时内 10,000的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!