时间戳

首页 > 解决方案 > 根据第二个表获取每个组的最大值记录

mysql - 根据第二个表获取每个组的最大值记录

问题描述

这与为每组分组的 SQL 结果获取具有最大值的记录有关，但必要的组位于单独的表中。

假设我有几栋楼，每栋楼都有一些办公室，我有一些人在这些办公室“签到”工作。

这张称为“offices”的表将建筑物与办公室匹配：

building   office
---
Seuss      Yertle
Seuss      Cubbins
Milne      Pooh
Milne      Eeyore
Milne      Roo

这张名为“checkins”的表记录了人们在每个办公室工作的时间：

id   office   person   timestamp
---
1    Yertle   Aaron    100
2    Cubbins  Aaron    200
3    Pooh     Aaron    300
4    Cubbins  Charlie  300
5    Cubbins  Aaron    700
6    Eeyore   Beth     600
7    Pooh     Beth     400

我想生成一个表格，告诉我，对于每个建筑物的人组合，哪个签到是该建筑物中那个人的最新签到：

 building  person   checkin_id  office   timestamp
 ---
 Seuss     Aaron    5           Cubbins  700
 Milne     Aaron    3           Pooh     300
 Milne     Beth     6           Eeyore   600
 Seuss     Charlie  4           Cubbins  300

我不知道该怎么做。标准技巧包括在比较相关值的同时将表连接到自身，然后丢弃没有更大值的行。我假设我需要两个“签到”副本和两个“建筑物”副本，它们之间有一个复杂的连接，但我似乎无法让 NULL 出现在正确的位置。

我正在使用 MySQL，如果有帮助的话。

标签： mysqlsqlgreatest-n-per-group

解决方案

有一个使用group_concat()and的技巧，substring_index()它允许你用一个来做到这一点group by：

select o.building, c.person,
       max(c.id) as checkinid,
       substring_index(group_concat(c.office order by timestamp desc), ',', 1) as office,
       max(c.timestamp) as timestamp
from offices o join
     checkins c
     on o.office = c.office
group by o.building, c.person;

这个版本假设id和timestamp增加一起，所以max()可以同时用于两者。

此外，group_concat()默认情况下，中间结果的字符数限制为大约 1,000 个字符，因此如果一个人/建筑物组合有很多办公室，或者办公室名称很长，这将不起作用。当然，如果office名称中出现逗号，则可以更改分隔符。

推荐阅读