mysql - 如何从单个 MySQL 表中获取各种条件下的最大值

Question

我有一堆(机器 id)mid 和(传感器 id)sid，以及它们对应的(值)v 的。不用说 id 列是唯一的行号。 (注意:表中还有其他列，并不是所有的 mid 都具有相同的 sid)

当前表:

+------+-------+-------+-----+---------------------+
|  id  |  mid  |  sid  |  v  |      timestamp      |
+------+-------+-------+-----+---------------------+
|  51  |  10   |   1   |  40 |  2015/5/1 11:56:01  |
|  52  |  10   |   2   |  39 |  2015/5/1 11:56:25  |
|  53  |  10   |   2   |  40 |  2015/5/1 11:56:42  |
|  54  |  11   |   1   |  50 |  2015/5/1 11:57:52  |
|  55  |  11   |   2   |  18 |  2015/5/1 11:58:41  |
|  56  |  11   |   2   |  19 |  2015/5/1 11:58:59  |
|  57  |  11   |   3   |  58 |  2015/5/1 11:59:01  |
|  58  |  11   |   3   |  65 |  2015/5/1 11:59:29  |
+------+-------+-------+-----+---------------------+

问:如何为每个 mid 获取每个 sid 的 MAX(v)？

预期输出:

+------+-------+-------+-----+---------------------+
|  id  |  mid  |  sid  |  v  |      timestamp      |
+------+-------+-------+-----+---------------------+
|  51  |  10   |   1   |  40 |  2015/5/1 11:56:01  |
|  53  |  10   |   2   |  40 |  2015/5/1 11:56:42  |
|  54  |  11   |   1   |  50 |  2015/5/1 11:57:52  |
|  56  |  11   |   2   |  19 |  2015/5/1 11:58:59  |
|  58  |  11   |   3   |  65 |  2015/5/1 11:59:29  |
+------+-------+-------+-----+---------------------+

预期的输出是为所有 mid 中的所有 sid 获取具有所有(单个)最大 v 值的整行>s.

附录:

由于表格很大，我需要用日期设置界限。对于上面的示例，两个边界日期应该是 2015/05/01 00:00:00(2015 年 5 月 1 日)到 2015/05/02 00:00:00(15 年 5 月 2 日)。 问:如何添加这个日期界限？

Answer 1

在子查询中为每个 mid、sid 组合找到最大 v，然后将其与您的原始表连接以获得所需的结果。

select *
from your_table t
join (
    select mid, sid, max(v) as v
    from your_table
    group by mid, sid
) t2 using (mid, sid, v);

这里注意，如果有多行的sid,mid,v相同，会全部返回。

如评论中所述，由于您有一个 id 列，您可以像这样将其包含在有限的相关查询中:

select *
from your_table t1
where id = (select id
            from your_table t2
            where t1.mid = t2.mid
            and t1.sid = t2.sid
            order by v desc, id desc
            limit 1
            );

这将为您提供每个 mid, sid 与最大 v 组合的单行(如果有关系，则为最新 id)。