I have a table that looks something like this
我有一张看起来像这样的桌子
|ID|TIME-|
|01|01:15|
|01|01:30|
|02|01:35|
|02|01:36|
|02|05:30|
|02|10:30|
|01|12:30|
What I want is to select distinct id only if they fall within the same hour mark, so the result I'm looking for is:
我想要的是只有当它们落在同一小时标记内时才选择不同的id,所以我要找的结果是:
|ID|TIME|
|01|1:15|
|02|1:35|
|02|5:30|
|02|10:30|
|01|12:30|
TIME is in mysql date format.
TIME是mysql日期格式。
how do I write this query?
我该如何写这个查询?
Thanks!
2 个解决方案
#1
0
Asuminig that TIME is a timestamp or datetime
Asuminig表示TIME是时间戳或日期时间
SELECT ID, TIME
FROM yourtable
GROUP BY ID, floor(TIME/3600)
this is mysql specific
这是mysql特有的
SELECT ID, TIME
FROM yourtable
GROUP BY ID, TIME DIV 3600
#2
0
You could do this. This will give you one record for each ID, hour showing the first time value for the given hour.
你可以做到这一点。这将为您提供每个ID的一条记录,小时显示给定小时的第一次值。
SELECT ID, TIME
FROM <yourtable>
GROUP BY ID, HOUR(TIME)
ORDER BY ID, TIME
#1
0
Asuminig that TIME is a timestamp or datetime
Asuminig表示TIME是时间戳或日期时间
SELECT ID, TIME
FROM yourtable
GROUP BY ID, floor(TIME/3600)
this is mysql specific
这是mysql特有的
SELECT ID, TIME
FROM yourtable
GROUP BY ID, TIME DIV 3600
#2
0
You could do this. This will give you one record for each ID, hour showing the first time value for the given hour.
你可以做到这一点。这将为您提供每个ID的一条记录,小时显示给定小时的第一次值。
SELECT ID, TIME
FROM <yourtable>
GROUP BY ID, HOUR(TIME)
ORDER BY ID, TIME