sql - 使用左连接 T-SQL 进行聚合和重复行

Question

我有基于此 SQL script (SQL Fiddle) 的数据，您可以在下面看到

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

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

• 计算一对EmitterIBAN和ReceiverIBAN进行的操作次数
• 计算每对EmitterIBAN和ReceiverIBAN的总和值

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

我做了一个选择，给出了情侣的数量和总和值，如 Query Result (SQL Fiddle)

SELECT Count(1)     AS NumberOperation, 
       emitteriban, 
       receiveriban, 
       Sum([value]) AS SumValues 
FROM   tableesperadocetransaction 
GROUP  BY emitteriban, 
          receiveriban 

我需要将名称和地址添加到结果中，因此我尝试像下面的请求一样进行左连接，我得到了这个 Result (SQL Fiddle)

SELECT * 
FROM   (SELECT Count(1)     AS NumberOperation, 
               emitteriban  AS _EmitterIban, 
               receiveriban AS _ReceiverIban, 
               Sum([value]) AS SumValues 
        FROM   tableesperadocetransaction 
        GROUP  BY emitteriban, 
                  receiveriban) tmp_T 
       LEFT JOIN tableesperadocetransaction 
              ON tableesperadocetransaction.emitteriban = tmp_T._emitteriban 
                 AND tableesperadocetransaction.receiveriban = 
                     tmp_T._receiveriban  

那么我如何纠正我的请求以获得我之前显示的结果。

PS:按名称、地址分组在我的情况下不起作用，因为我的夫妇没有某个地址

Answer 1

您可以将 Emitter 和 Address 列添加到第一个 select 语句(以及 GROUP BY 部分):

select  count(1) as NumberOperation, 
Emitter, 
ReceiverAddress,
EmitterIban, 
ReceiverIban,
SUM([Value]) as SumValues
FROM TableEsperadoceTransaction
Group By EmitterIban,
         ReceiverIban, 
         Emitter,
         ReceiverAddress