MySql。谁能回答为什么这会产生超出预期范围的结果？

Question

SELECT mt.mB, mt.UTC
from thundersense mt

where mt.mB =
    (   SELECT max(mB)
        FROM thundersense mt2
        WHERE mt2.UTC > 1490569200
    )

这会产生一些结果，但大多数都在所需日期之前。如何选择当天的数据和时间。我确信有人问过这个问题，但我无法找到它......

Answer 1

过滤器也需要包含在外部查询中......

SELECT mt.mB, mt.UTC
from thundersense mt
where mt.UTC > 1490569200
and mt.mB =
    (   SELECT max(mB)
        FROM thundersense mt2
        WHERE mt2.UTC > 1490569200
    );

下面的查询生成一次 max(mB)，而不是每一行...

select mt.mB,
       mt.UTC
from thundersense mt
join (  select max(mB) as mB
        from thundersense
        where UTC > 1490569200 ) as max on (mt.mB = max.mB)
where mt.UTC > 1490569200;