mysql - 获取数据组中的最小最大值

Question

在 sql server 中我有一个表有超过千万条记录，比如

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

Answer 1

Now there are some changes in requirement i want is in same table i want is top two max values

如果您使用的是 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 的 Min-/Max 值，您可以使用 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