时间戳

首页 > 解决方案 > 如何获取特定日期范围的最新数据？

mysql - 如何获取特定日期范围的最新数据？

问题描述

我有通过以下查询获得的示例产品表

select * from table where timestamp BETWEEN '2018-01-10 00:00:00' AND '2018-01-11 23:59:59

id timestamp            product
1  10-1-2018:10:02:02   a
2  10-1-2018:10:02:03   b
3  11-1-2018:10:02:05   a
4  11-1-2018:10:02:09   b
5  11-1-2018:10:02:19   a
6  11-1-2018:10:02:23   b

所需输出

id timestamp            product
1  10-1-2018:10:02:02   a
2  10-1-2018:10:02:03   b
5  11-1-2018:10:02:19   a
6  11-1-2018:10:02:23   b

我尝试了以下查询

第一个查询给我 1,2,3,4 作为结果

  1. select * from (select * from table where timestamp BETWEEN '2018-01-10 00:00:00' AND '2018-01-11 23:59:59') AS q group by Date(timestamp), product

  2. select id, max(timestamp), product from (select * from table where timestamp BETWEEN '2018-01-10 00:00:00' AND '2018-01-11 23:59:59') AS q group by Date(timestamp), product

我的问题是如果我有此类数据，如何获取特定日期范围的最新数据？

标签： mysql

解决方案

考虑以下：

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,timestamp DATETIME NOT NULL
,product CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
(1,'2018-01-10:10:02:02','a'),
(2,'2018-01-10:10:02:03','b'),
(3,'2018-01-11:10:02:05','a'),
(4,'2018-01-11:10:02:09','b'),
(5,'2018-01-11:10:02:19','a'),
(6,'2018-01-11:10:02:23','b');

SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT product
            , MAX(timestamp) timestamp 
         FROM my_table 
        GROUP 
           BY product
            , DATE(timestamp)
     ) b 
    ON b.product = a.product 
   AND b.timestamp = a.timestamp;
+----+---------------------+---------+
| id | timestamp           | product |
+----+---------------------+---------+
|  1 | 2018-01-10 10:02:02 | a       |
|  2 | 2018-01-10 10:02:03 | b       |
|  5 | 2018-01-11 10:02:19 | a       |
|  6 | 2018-01-11 10:02:23 | b       |
+----+---------------------+---------+

顺便说一句，这是 SO 上此标签下最常见的一个问题。

推荐阅读