mysql - 如何获取每个员工最多两条记录

Question

我在数据库中有一个表，其记录如下所示:

Id | EmpName  | LeaveDate               | createdDate

1  | Govind   | 2014-04-02 00:00:00.000 | 2014-04-02
2  | Aravind  | 2014-04-03 00:00:00.000 | 2014-04-05
3  | Govind   | 2014-04-04 00:00:00.000 | 2014-04-10
4  | Amar     | 2014-04-05 00:00:00.000 | 2014-04-11
6  | Aravind  | 2014-04-06 00:00:00.000 | 2014-04-16
7  | Govind   | 2014-04-07 00:00:00.000 | 2014-04-16
8  | Aravind  | 2014-04-08 00:00:00.000 | 2014-04-16
9  | Amar     | 2014-04-09 00:00:00.000 | 2014-04-16
10 | Aravind  | 2014-04-10 00:00:00.000 | 2014-04-16
11 | Govind   | 2014-04-11 00:00:00.000 | 2014-04-16
12 | Aravind  | 2014-04-12 00:00:00.000 | 2014-04-16
13 | Amar     | 2014-04-13 00:00:00.000 | 2014-04-16
14 | Aravind  | 2014-04-14 00:00:00.000 | 2014-04-16

现在，我想显示所有员工的最后两条记录

示例输出:

Id | EmpName  | LeaveDate                | createdDate


11 | Govind   | 2014-04-11 00:00:00.000 | 2014-04-16
7  | Govind   | 2014-04-07 00:00:00.000 | 2014-04-16
14 | Aravind  | 2014-04-14 00:00:00.000 | 2014-04-16
12 | Aravind  | 2014-04-12 00:00:00.000 | 2014-04-16
13 | Amar     | 2014-04-13 00:00:00.000 | 2014-04-16
9  | Amar     | 2014-04-09 00:00:00.000 | 2014-04-16

我使用 JPA，很难编写满足以下要求的查询。谁能帮帮我

Answer 1

对于sql-server。

使用CTE。

;with cte as
(
    select rn = row_number() over
    (
      partition by EmpName
      order by LeaveDate desc
    ),*
from employees
)
select * from cte
where rn <= 2;

SQL Fiddle

<小时/>

对于mysql。

通过添加行号。

select t2.Id,
t2.EmpName,
t2.LeaveDate,
t2.createdDate
from
(
   select Id,
   EmpName,
   LeaveDate,
   createdDate,
   ( 
       case EmpName 
       when @curA
       then @curRow := @curRow + 1 
       else @curRow := 1 and @curA := EmpName end
    ) + 1 as rn
    from employees t,
    (select @curRow := 0, @curA := '') r
    order by EmpName,LeaveDate desc
)t2
where t2.rn<3;

SQL Fiddle

<小时/>

下面的 sql 查询适用于 mysql 和 sql-server。

查询

select *
from employees t1
where 
(
  select count(*) from employees t2
  where t2.EmpName = t1.EmpName
  and t2.LeaveDate > t1.LeaveDate
) <= 1
order by t1.EmpName;