mysql - 选择每组的最后 30 个项目

Question

希望标题有意义。

对于这个例子，我将在我的数据库中有下一个表

measurements
==================================
stn | date        | temp | time  =
1   | 01-12-2001  | 2.0  | 14:30 =
1   | 01-12-2001  | 2.1  | 14:31 =
1   | 03-12-2001  | 1.9  | 21:34 =
2   | 01-12-2001  | 4.5  | 12:48 =
2   | 01-12-2001  | 4.7  | 12:49 =
2   | 03-12-2001  | 4.9  | 11:01 =
==================================

等等等等。

每个站 (stn) 都有许多测量值，每 day second 一次。现在我想选择最近 30 days measurements 每个站点的温度，其中站点至少有 30 个温度测量值。

我在玩子查询和分组依据，但我似乎无法弄清楚。

希望有人能帮帮我。

编辑了表格我的示例过于简单，遗漏了一条关键信息。请复习问题。

Answer 1

select t1.stn,t1.date,t1.temp,t1.rn from (
select *,
   @num := if(@stn = stn, @num + 1, 1) as rn,
   @stn := stn as id_stn
from table,(select @stn := 0, @num := 1) as r
order by stn asc, date desc) as t1
inner join (select `stn`
           from table
          where concat_ws(' ',date,time) >= now() - interval 30 day
          group by `stn`
         having count(*) >= 30) as t
on t1.stn = t.stn
and t1.rn <= 30
order by stn,date desc,time desc