根据datetime和using name获取最后一行

Question

我有以下MYSQL表:

DATETIME dt
VARCHAR location
FLOAT temperature

此表包含在不同位置测量的许多温度。现在我需要一个SQL查询来获取每个位置的最后一次测量。

我不知道该怎么做

Answer 1

注意，在执行group by aggregate操作时，最好先选择列，然后执行group by.

select
t1.location,
t2.t2_dt as dt,
t1.temperature 
from table_name t1 
join
(
  select
  location,
  temperature,
  max(dt) as t2_dt
  from table_name
  group by location
)t2
on t2.location = t1.location AND t1.dt = t2.t2_dt
group by t1.location

Answer 2

这将完成您的工作

    select * from
    (select location, temperature, dt from my_table) as tab
    join
    (select max(dt) as max_dt from my_table
    group by location) as max_date_tab
    on (tab.dt = max_date_tab.max_dt)