sql - 在 SQL Server 中查找任意 3 行的列的最大总和

Question

我想问以下问题

我有一个包含很多行和以下列的表格

sender, recipient, value

例如

sender  recipient   value
smith   williams     500
smith   taylor       100
smith   vagelis      200
vagelis taylor       150
giorgos taylor       150
nikos   taylor       200
smith   Kostas       300

我想找出任意 3 行和所有特定发件人的最大值总和。

以我为例

smith   800
giorgos 150
vagelis 150
nikos   200

最后找到所有发件人的任意 3 行的最大总和超过 500。这是我的最终目标。

以我为例

smith 800

提前致谢

更新我在@Prdp 帖子的帮助下找到了解决方案

select distinct a.sender,a.total_sum from(
 SELECT t1.sender,(t1.value+ t2.value + t3.value)
       as total_sum
    FROM    
    transfers2  t1, transfers2 t2, transfers2 t3
          where 
              t1.sender = t2.sender
           and t3.sender = t2.sender
             and t1.date!=t2.date 
             and t1.date!=t3.date
             and t2.date!=t3.date
             ) a
             where a.total_sum>500

Answer 1

另一种方法(效率不高)。 Gordan 的方法比这个好很多

SELECT Distinct sender
FROM   (SELECT t1.sender,
               CASE cnt
                 WHEN 1 THEN t1.value
                 WHEN 2 THEN t1.value + t2.value
                 ELSE t1.value + t2.value + t3.value
               END total_sum
        FROM   (SELECT sender,value,Count(1) OVER(partition BY sender) AS cnt FROM yourtable) t1
               JOIN yourtable t2
                 ON t1.sender = t2.sender
               JOIN yourtable t3
                 ON t3.sender = t2.sender) a 
Where total_sum > 500

您可以通过添加Where 子句来过滤发件人

更新:基于编辑，您只需要简单的Group by 和Having 子句

Select sender,sum(value)
From yourtable 
Group by sender
Having sum(value) > 500