>我有这个数据
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
我想得到下面这样的结果
- 计算几个发射器IBAN和接收器IBAN的操作次数
- 计算每对发射器IBAN和接收器IBAN的总和值
- 并按地址分组，通过获取最大地址值可以不同的地址
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 个索引
- Date_Operation
- 发射器伊班
- 接收者伊班
你可以尝试这样的事情：
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)。使用三列索引，您应该非常快速地获取地址值。
我认为你应该使用窗口函数(SQL 2012+(：
Select count(1) as NumberOperation,
MAX(t.Emitter) as EmitterName,
t.EmitterIban,
MAX(t.Receiver) as ReceiverName,
t.ReceiverIban,
FIRST_VALUE(x.RecieverAddress) OVER (PARTITION BY t.EmitterIban, t.ReceiverIban ORDER BY Data_Opera DESC),
SUM(t.[Value]) as SumValues
FROM TableEsperadoceTransaction AS t
Group By t.EmitterIban,
t.ReceiverIban;
我在 CTE 中使用了 row_number((，聚合自连接：
with CTE as
(
select t1.*, row_number() over(partition by EmitterIban, ReceiverIban order by Date_Opera desc) as rn
from TableEsperadoceTransaction t1
)
select a1.EmitterIban,a1.emitter as EName,
a1.ReceiverIban, a1.receiver as RName,
a1.ReceiverAddress
max(a2.rn) as NumberOperation,
sum(a2.value) as SumValues
from CTE a1
inner join CTE a2
on a1.EmitterIban = a2.EmitterIban
and a1.ReceiverIban = a2.ReceiverIban
where a1.rn = 1
group by a1.EmitterIban,a1.emitter,
a1.ReceiverIban, a1.receiver,
a1.ReceiverAddress