最小/最大时间戳后跟特定日期

vsikbqxv  于 2021-08-09  发布在  Java
关注(0)|答案(3)|浏览(389)

表包括以下内容:

+------+----------------+--------------------------------------+
| ID   | Date           | Timestamp                            |
+------+----------------+--------------------------------------+
| 1    | 01-01-2020     | 01-01-2020 10:00:00                  |
| 1    | 06-01-2020     | 06-01-2020 23:00:00                  |
| 1    | 03-02-2020     | 03-02-2020 10:00:00                  |
| 2    | 06-01-2020     | 06-01-2020 12:49:00                  |
| 2    | 07-03-2020     | 07-03-2020 10:51:00                  |
| 3    | 23-01-1992     | 23-01-1992 09:00:00                  |
| 3    | 23-01-1992     | 23-01-1992 10:00:00                  |
+------+----------------+--------------------------------------+

我想得到最小时间戳日期为'06-01-2020'的id,答案应该是id#2

SELECT

ID,
MIN(Timestamp)

FROM Table
WHERE Date = '06-01-2020'
GROUP BY ID

我对上述的理解是,在得到最小时间戳之前,首先过滤日期,这会导致id#1和#2的结果不正确。请告知。

v1uwarro

v1uwarro1#

请尝试首先在整个表中查找最小值,然后限制到感兴趣的日期:

SELECT ID, min_timestamp
FROM
(
    SELECT ID, MIN(Timestamp) AS min_timestamp
    FROM yourTable
    GROUP BY ID
) t
WHERE Date = '2020-06-01';
dxpyg8gm

dxpyg8gm2#

你似乎想要两个条件: MIN(timestamp) 每个id ID 在特定日期有记录的
你可以 HAVING :

SELECT ID, MIN(Timestamp)
FROM Table
GROUP BY ID
HAVING SUM(CASE WHEN Date = '2020-06-01' THEN 1 ELSE 0 END) > 0;

这是由 id 并返回最小时间戳。然后它过滤ID,以便只过滤日期为2020-06-01的ID。
如果你 '2020-06-01' 是表中最新的日期,可以简化为:

HAVING MAX(Date) = '2020-06-01'
o75abkj4

o75abkj43#

这将返回ID,其最小时间戳为06-01-2020

SELECT
    ID,
    MIN(Timestamp)
FROM Table
GROUP BY ID
HAVING min(Date) = date '2020-01-06'

或者不带日期列

HAVING cast(min(timestamp as date)) = date '2020-01-06'

相关问题