获取给定物品编号的最近两个查询结果的Mysql查询

Question

我在我的数据库中有以下属性。

统计ID、设备名称、数值、时间戳。

对于给定的统计数据，我想要找到每个唯一设备的最近的两个时间戳和相应的值。

我正在尝试以下方法：

尝试1)

select statistic, devicename, value, timestamp
from X_STATSVALUE
where statistic=19
order by orgtime DESC limit 2;

这让我得到了前两个时间戳，但并非每个设备的。

试用2）

select statistic, devicename, value, timestamp
from X_STATSVALUE as x 
where x.statistic=241
  and (select count(*)
       from X_STATSVALUE as y
       where y.statistic=x.statistic
         and y.device=x.device
         and y.timestamp > x.timestamp) <=1;

但是这也不太有效...

基本上，我想要每个设备在给定统计数据中的最近两个时间戳和值。任何帮助都将不胜感激 :)

- sujatha balaraman

Answer 1

这是我解决这类问题的方法：

SELECT x.statistic, x.devicename, x.value, x.timestamp
FROM X_STATSVALUE AS x
  LEFT OUTER JOIN X_STATSVALUE AS x2
    ON (x.statistic = x2.statistic 
    AND x.devicename = x2.devicename 
    AND x.timestamp < x2.timestamp)
GROUP BY x.statistic, x.devicename
HAVING COUNT(*) < 2;

换句话说，显示行的条件是：同一statistic和devicename下，有不到两行数据时间戳timestamp更大（更近）。

我假设给定统计量和设备名称时，timestamp列中不会出现重复值。

- Bill Karwin

---

那只能在MySQL中运作。其他关系型数据库会告诉你不能仅按选定语句的一部分进行分组，而是必须按所有四个进行分组。 - mat

问题是关于MySql。 - Sunny Milenov

Answer 2

尝试类似这样的内容：

SELECT DISTINCT x.statistic, x.devicename, x.value, x.timestamp
FROM X_STATSVALUE AS x
WHERE x.timestamp IN (SELECT timestamp
                      FROM X_STATSVALUE
                      WHERE devicename = x.devicename AND statistic = x.statistic
                      ORDER BY timestamp LIMIT 2)

（虽然在旧版MySQL中可能无法正常运行）

- mat

Answer 3

我在我的系统上使用类似于你的演示表的以下查询进行了测试，它可以正常工作。我考虑了orgtime而不是timestamp..你也可以这样做（只需要更改名称）。

    select t1.statistic, devicename, value, timestamp from X_STATSVALUE as t1 

    inner join 

    ( select statistic, max(orgtime) as orgtime from X_STATSVALUE group by statistic ) 
as t2 

    on t1.statistic = t2.statistic and t1.orgtime = t2.orgtime

    UNION

    select tb1.statistic, tb1.devicename, tb1.value, tb1.timestamp
    from X_STATSVALUE as tb1 inner join 

    ( select statistic, max(orgtime) as orgtime from X_STATSVALUE  WHERE statistic+orgtime not in 
    (select t1.statistic+t1.orgtime from X_STATSVALUE as t1 inner join 
    ( select statistic, max(orgtime) as orgtime from X_STATSVALUE group by statistic ) 
as t2 on t1.statistic = t2.statistic and t1.orgtime = t2.orgtime
    ) group by statistic 
    ) 

    as tb2 on tb1.statistic = tb2.statistic and tb1.orgtime = tb2.orgtime

- Samiksha