mysql - 为每个不同的名称显示表中最近添加的行

Question

假设这是我的表模式

Create Table PowerReading
{ Device VARCHAR(32);
  Power INT;
  Time TIMESTAMP;
}

假设这些是我表中的行

Device | Power | Time
A3     |   5   | 2013-05-01 17:36:00
A3     |   9   | 2013-05-01 17:37:44
B1     |  11   | 2013-05-01 17:35:14
B1     |   5   | 2013-05-01 17:35:55
B7     |   4   | 2013-05-01 17:34:12
B7     |   0   | 2013-05-01 17:34:44

我花了几天时间试图弄清楚如何显示每个 DISTINCT 设备名称的最新读数。我想要一个 SQL 查询来提供上表的 THIS 输出。

 Device | Power | Time
A3     |   9   | 2013-05-01 17:37:44
B1     |   5   | 2013-05-01 17:35:55
B7     |   0   | 2013-05-01 17:34:44

我试过用下面的代码来完成，但是没用

SELECT * FROM (SELECT Device,Power,MAX(Time) as Max FROM PowerReading GROUP
BY Device,Power) t1 JOIN PowerReading on t1.Device=PowerReading.Device AND      
t1.max=PowerReading.Power

上面的代码给我的输出不是我想要的输出这个小问题困扰我好几天了。请帮我？ :-)

Answer 1

派生表还在 power 列上使用了 group by，这是不需要的。

SELECT p.* FROM 
(SELECT Device, MAX(Time) as maxtime 
 FROM PowerReading
 GROUP BY Device) t1 
JOIN PowerReading p on t1.Device = p.Device AND t1.maxtime = p.time