没有group by的SQL Max(日期)

Say I have the following table...

假设我有下面这张桌子……

MemberID  ServDate
001       12-12-2015
001       12-13-2015
001       12-15-2015
002       11-30-2015
002       12-04-2015

And I want to make it look like this...

我想让它看起来像这样…

MemberID  ServDate     LastServDate
001       12-12-2015   12-15-2015
001       12-13-2015   12-15-2015
001       12-15-2015   12-15-2015
002       11-30-2015   12-04-2015
002       12-04-2015   12-04-2015

Is there way I can do this without having to use a GROUP BY or nested query? (I'm dealing with a very large database and the GROUP BY slows things down considerably)

我是否可以在不使用GROUP BY或嵌套查询的情况下做到这一点?(我正在处理一个非常大的数据库，而这个组大大降低了速度)

Thanks!

谢谢!

1 个解决方案

#1

SELECT 
  MemberID, ServDate, 
  MAX(ServDate) OVER (PARTITION BY MemberID) AS LastServDate
FROM Table

Standard SQL, so works in most modern RDBMS (including SQL Server and Oracle).

标准SQL，在大多数现代RDBMS(包括SQL Server和Oracle)中工作。

EDIT by the way, if you want to learn more: MSDN ref. for OVER

顺便说一下，如果你想了解更多:MSDN ref. for OVER

#1

SELECT 
  MemberID, ServDate, 
  MAX(ServDate) OVER (PARTITION BY MemberID) AS LastServDate
FROM Table