在sql server中,我有一个表,其中有超过crores的记录,如
empnos date
a1 18-Jul-13
a1 18-Jul-13
a1 18-Jul-13
a1 18-Jul-13
a2 18-Jul-13
a2 18-Jul-13
a2 18-Jul-13
a3 18-Jul-13
a1 19-Jul-13
a2 19-Jul-13
a3 19-Jul-13
a1 20-Jul-13
a2 20-Jul-13
a3 20-Jul-13
现在我想要输出在它被分组的地方,每个分组都有最小最大值,所以输出应该像下面的1
EMPNO DATE
A1 18-Jul-13
A1 20-Jul-13
A2 18-Jul-13
A2 20-Jul-13
A3 18-Jul-13
A3 20-Jul-13
最佳答案
现在有一些变化,我想在同一张表中
want是前两个最大值
如果您使用的是SQL Server,则可以使用ROW_NUMBER
或DENSE_RANK
:
WITH CTE AS(
SELECT empnos,
date,
rn = row_number() over (partition by empnos order by date desc)
FROM dbo.TableName
)
SELECT * FROM CTE WHERE RN <= 2
Demo
如果还需要每个
empnos
的最小/最大值,可以使用OVER
子句:WITH CTE AS(
SELECT empnos,
date,
min = Min(date) over (partition by empnos),
max = Max(date) over (partition by empnos),
rn = row_number() over (partition by empnos order by date desc)
FROM dbo.TableName
)
SELECT * FROM CTE WHERE RN <= 2
Demo