mysql select sum, max, and another column value at the same row which the max value 停留

Question

示例表:

uid time_stp                traf
1   2016-01-13 00:00:00     6
1   2016-01-13 05:00:00     8
1   2016-01-13 10:00:00     14
1   2016-01-13 15:00:00     26
1   2016-01-13 20:00:00     42
...

我的SQL:

select
    uid,
    from_unixtime(floor(unix_timestamp(time_stp)/3600) * 3600) as tm,
    sum(traf),
    max(traf),
    -- xxx as max_traf_time
from
    group_by_time_range
group by
    uid, tm;

我想对每小时的traf进行求和，并在每个小时部分找到最大traf和对应的time_stp。我怎么可能用一条 SQL 语句来完成这些东西。

Answer 1

你试过吗

select
    T1.uid,
    from_unixtime(floor(unix_timestamp(time_stp)/3600) * 3600) as tm,
    sum(traf),
    (
     SELECT max(traf)
     FROM group_by_time_range T2
     WHERE T2.uid = T1.uid
    ) as Max_Traf,
    (
     SELECT time_stp
     FROM group_by_time_range T3
     WHERE traf = Max_Traf
    ) as max_traf_time
from
    group_by_time_range T1
group by
    uid, tm;