我正在尝试为我的表构建个性化查询,该查询包括为每个人获取最新的N条记录。我的表架构如下:

+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| person    | varchar(100)        | NO   | PRI | NULL    |       |
| time      | bigint(20) unsigned | NO   | PRI | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+


例如,如果我有以下数据:

+---------+-------+
| person  | time  |
+---------+-------+
| A       | 2     |
| A       | 7     |
| B       | 1     |
| B       | 6     |
| B       | 4     |
| C       | 3     |
+---------+-------+


和我的N = 2,预期结果将是:

+---------+-------+
| person  | time  |
+---------+-------+
| A       | 7     |
| A       | 2     |
| B       | 6     |
| B       | 4     |
| C       | 3     |
+---------+-------+


(每个人的最后2个结果,按升序排列,时间按降序排列)

我知道我可以首先获得所有人员的姓名,然后逐个选择/排序/限制他们,但是我很好奇我是否可以在一个查询中做到这一点,并让DBMS进行艰苦的工作。对于N = 1,我成功使用了max和group by语句:

SELECT person, max(time) as time FROM table GROUP BY person ORDER BY person ASC, time DESC


但是我不知道如何将其扩展为更大的N值。您能帮我吗?

最佳答案

好的,我使用@Rudie链接找到了解决方案:

select * from (
    select a.* from points as a
    left join points as a2
    on a.person= a2.person and a.time <= a2.time
    group by person, time
    having count(*) <= 2) a
order by person asc, time desc;


实时示例:http://sqlfiddle.com/#!2/58026/2

运行良好。谢谢大家的贡献!

关于mysql - 获取每个人的最新N个结果,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23940764/

10-12 20:36