sql - 根据分组依据中特定列的最大值从一行中选择一条信息？

Question

我有这个数据

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Id| Date_Opera | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     Value 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1, | 2017-07-07 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0002,        121.72
2, | 2017-09-27 | Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
3, | 2017-10-17 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0048,        51.81
4, | 2017-05-01 | Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
5, | 2017-11-17 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0001,        133.99
6, | 2017-10-10 | Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30
7, | 2017-07-18 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0004,        186.99

我想得到像下面这样的结果

• 计算两个EmitterIBAN和ReceiverIBAN进行的操作数
• 计算每对 EmitterIBAN 和 ReceiverIBAN 的总和值
• 并通过取最大地址值对可以不同的地址进行分组

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sum| Date_Opera | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     SumValue 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4, | 2017-11-17 |  Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point           0048,      494,51
1, | 2017-09-27 |  Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
1, | 2017-05-01 |  Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
1, | 2017-10-10 |  Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30

为了得到这个结果，我使用了这个请求

Select  count(1) as NumberOperation, 
        MAX(Emitter) as EmitterName, 
        EmitterIban, 
        MAX(Receiver) as ReceiverName, 
        ReceiverIban,
        MAX(ReceiverAddress) as ReceiverAddress,
        SUM([Value]) as SumValues
FROM TableEsperadoceTransaction
Group By EmitterIban,
         ReceiverIban

但是现在，我想要的是，我不想像之前的例子那样获取最大地址，而是想从具有最大数据时间操作的记录中获取地址。这是我的数据结果的一个例子，应该是什么样子

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sum| Date_Opera | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     SumValue 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4, | 2017-11-17 |  Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point           0002,      494,51
1, | 2017-09-27 |  Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
1, | 2017-05-01 |  Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
1, | 2017-10-10 |  Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30

所以我的问题是我该如何做这样的请求？

PS:我有2.4亿条记录

编辑:我有 3 个索引

1. 日期_操作
2. EmitterIban
3. ReceiverIban

Answer 1

你可以尝试这样的事情:

Select  count(1) as NumberOperation, 
        MAX(t.Emitter) as EmitterName, 
        t.EmitterIban, 
        MAX(t.Receiver) as ReceiverName, 
        t.ReceiverIban,
        (SELECT TOP 1 x.RecieverAddress 
         FROM TableEsperadoceTransaction AS x 
         WHERE x.EmitterIban=t.EmitterIban AND x.RecieverIban=t.RecieverIban
         ORDER BY Data_Opera DESC) as ReceiverAddress,
        SUM(t.[Value]) as SumValues
FROM TableEsperadoceTransaction AS t
Group By t.EmitterIban,
         t.ReceiverIban;

我用一个子选择替换了你的 MAX(Address) 获取最上面的地址，由 Data_Opera 以相同的条件排序...

顺便说一句:在您的日期列上放置一个索引会有所帮助...

更新:这可能会更快......

Select  count(1) as NumberOperation, 
        MAX(t.Emitter) as EmitterName, 
        t.EmitterIban, 
        MAX(t.Receiver) as ReceiverName, 
        t.ReceiverIban,
        (SELECT TOP 1 x.RecieverAddress 
         FROM TableEsperadoceTransaction AS x 
         WHERE x.EmitterIban=t.EmitterIban 
           AND x.RecieverIban=t.RecieverIban
           AND x.Data_Opera=MAX(t.Data_Opera)) as ReceiverAddress,
        SUM(t.[Value]) as SumValues
FROM TableEsperadoceTransaction AS t
Group By t.EmitterIban,
         t.ReceiverIban;

GROUP BY 将允许您直接获取 MAX(t.Data_Opera)。使用三列索引，您应该可以非常快速地获取地址值。