选择每组的最后 30 个项目

2022-01-23 00:00:00 limit group-by subquery mysql

希望标题有意义.

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

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 =
==================================

等等等等.

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

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.

希望有人可以帮助我.

修改了表格我的例子过于简单化了，留下了一条关键信息.请检查问题.

edited the table My example was oversimplified leaving a critical piece of information out. Please review the question.

推荐答案

这是应该选择 Last 30 个条目的查询，其中一个站至少有 30 个条目

这个查询是基于 nick rulez 的回答，所以请给他点赞

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.

要了解有关此类查询的更多信息，请查看此处组内配额(前 N 个每组)

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

相关文章