如何从单个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 alue。

补遗：

由于表非常大，我需要用日期设置边界。对于上述样本，两个边界日期应2015/05/01 00:00:00(15年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
            );

这将给你一个单行每个中间，sid 组合与最大 v(在平局的情况下是最新的 id(。

Answer 2

将MAX()函数与GROUP BY子句一起使用

SELECT id,  mid,  sid, MAX(v) AS v, `timestamp` 
FROM MyTable 
GROUP BY mid,  sid;

这将返回每个 mid 和 sid 组合的最大值为 v 的行。