mysql - 如何获取所选组的最新N条记录

Question

我想在 MySql version 5.1.9 上运行查询，该查询仅返回所选部门的前两个(按 JoiningDate 排序)。

例如，我的数据如下:

+-------+------------------------------------------+----------+------------+
| empid | title                                    | Dept     | JoiningDate|
+-------+------------------------------------------+----------+------------+
|   1   | Research and Development                 | 1        | 2015-08-06 |
|   2   | Consultant                               | 2        | 2015-08-06 |
|   3   | Medical Consultant                       | 3        | 2015-08-06 |
|   4   | Officer                                  | 4        | 2015-08-06 |
|   5   | English Translator                       | 5        | 2015-08-06 |
|   6   | Teacher                                  | 1        | 2015-08-01 |
|   7   | Physical Education                       | 2        | 2015-08-01 |
|   8   | Accountant                               | 3        | 2015-08-01 |
|   9   | Science Teacher                          | 4        | 2015-08-01 |
|  10   | Home Science                             | 5        | 2015-08-01 |
|  11   | Research Assistant                       | 1        | 2015-08-05 |
|  12   | Consultant                               | 2        | 2015-08-05 |
|  13   | Consultant HR                            | 3        | 2015-08-05 |
|  14   | Technical Lead                           | 4        | 2015-08-05 |
|  15   | Hindi Translator                         | 5        | 2015-08-05 |
|  16   | Urdu Teacher                             | 1        | 2015-08-02 |
|  17   | Physical Education                       | 2        | 2015-08-02 |
|  18   | Accountant                               | 3        | 2015-08-02 |
|  19   | Science                                  | 4        | 2015-08-02 |
|  20   | Home Science                             | 5        | 2015-08-02 |
+-------+------------------------------------------+----------+------------+

我希望查询输出 Dept (1,2,3) 的最新连接的两个 empid，即:

+-------+------------------------------------------+----------+------------+
| empid | title                                    | Dept     | JoiningDate|
+-------+------------------------------------------+----------+------------+
|   1   | Research and Development                 | 1        | 2015-08-06 |
|  11   | Research Assistant                       | 1        | 2015-08-05 |
|   2   | Consultant                               | 2        | 2015-08-06 |
|  12   | Consultant                               | 2        | 2015-08-05 |
|   3   | Medical Consultant                       | 3        | 2015-08-06 |
|  13   | Consultant HR                            | 3        | 2015-08-05 |
+-------+------------------------------------------+----------+------------+

Answer 1

在mysql中你可以使用用户定义的变量来达到你想要的结果

SELECT 
t.empid,
t.title,
t.Dept,
t.JoiningDate
FROM
(
    SELECT 
    *,
    @r:= CASE WHEN @g = b.Dept THEN @r + 1 ELSE 1 END rounum,
    @g:= b.Dept
    FROM (
        SELECT * 
        FROM table1 
        CROSS JOIN (SELECT @r:= NULL,@g:=NULL) a
        WHERE Dept IN(1,2,3)
        ORDER BY Dept,JoiningDate DESC
    ) b
) t
WHERE t.rounum <=2

DEMO