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屋！