通过选择每组最后30个项目 [英] Select last 30 items per group by

问题描述

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

 尺寸
 ================================= = 
 stn |日期| temp |时间= 
 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   。现在，我要选择最近30天 测量结果中每个测站的温度，其中测站至少有30个温度测量值。 
 
 
我正在玩subquerys和group by，但我似乎无法弄清楚。 
 
 
 
希望有人能帮助我。
 
 
 
 编辑表格 
我的例子被过分简化，留下了一条重要的信息。请检查问题。
解决方案
这是应该选择的最后30个条目一个电台至少有30个条目 
 
 
 
这个查询基于 nick rulez ，所以请让他高兴起来 
 
 
  SELECT t1.stn，t1.date，t1.temp，t1.time FROM 
（
 SELECT *，
 @num：= if（@stn = stn，@num + 1，1）为rn，
 @stn：= stn为id_stn 
 FROM 
`tablename`，
（SELECT @stn：= 0，@num：= 1）as r 
 ORDER BY stn asc，date desc 
）as t1 
 INNER JOIN 
（
 SELECT`stn` 
 FROM`tablename` 
 GROUP BY`stn` 
 HAVING COUNT（*）> = 30 
 ）as t 
 ON t1.stn = t.stn 
 AND t1.rn <= 30 
 ORDER BY stn，date desc，time desc 
  
我已经在示例数据库I m上对它进行了测试ade基于你的模式，工作正常。
 
 
 
要了解更多关于这样的查询，请看这里组内配额（每组最多N个） 
 
Hopefully the title makes any sense.

For this example I'll have the next table in my database
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 =
==================================
And so on and so forth.

Each station (stn) has many measurements, one per day second. Now I want to select the temp of each station of the last 30 days measurements where the station has at least 30 temperature measurements.

I was playing with subquerys and group by, but I can't seem to figure it out. 

Hope someone can help me out here.

edited the table
My example was oversimplified leaving a critical piece of information out. Please review the question.
解决方案
This is the query that should select Last 30 entries where there are at least 30 entries for a station  

This query is based on the answer here by nick rulez, so please upvote him
SELECT t1.stn, t1.date, t1.temp, t1.time FROM 
    (
        SELECT *,
            @num := if(@stn = stn, @num + 1, 1) as rn,
            @stn := stn as id_stn
        FROM 
            `tablename`, 
            (SELECT @stn := 0, @num := 1) as r
        ORDER BY stn asc, date desc
    ) as t1
INNER JOIN 
    (
        SELECT `stn`
        FROM `tablename` 
        GROUP BY `stn`
        HAVING COUNT(*) >= 30
    ) as t
ON t1.stn = t.stn
AND t1.rn <= 30
ORDER BY stn, date desc, time desc
I have tested it on a sample database I made based on your schema and is working fine.

To know more about such queries have a look here Within-group quotas (Top N per group)

                        
这篇关于通过选择每组最后30个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！