我有两张桌子:
连接器_状态
连接器 | status_timestamp | status |
---|---|---|
1 | 2020-03-03 09:07:09.058000 | available |
2 | 2020-03-03 09:51:03.852000 | faulted |
1 | 2022-10-06 16:32:14.130000 | charging |
3 | 2022-10-06 16:28:26.228000 | available |
4 | 2022-10-06 16:28:03.195000 | charging |
连接器
连接器 | box_id | 连接器_id |
---|---|---|
1 | Alpha | 0 |
2 | Alpha | 1 |
3 | Beta | 0 |
4 | Beta | 1 |
My 连接器_状态 table has multiple rows for each 连接器, but I only want the most recent row, based on the box_id
I would like to join the tables based on box_id but using the latest timestamp from the 2x 连接器s. This would select status charging based on the above table
连接表看起来有点像这样:
连接器 | status_timestamp | status | box_id |
---|---|---|---|
1 | 2020-03-03 09:07:09.058000 | available | Alpha |
2 | 2020-03-03 09:51:03.852000 | faulted | Alpha |
1 | 2022-10-06 16:32:14.130000 | charging | Alpha |
3 | 2022-10-06 16:28:26.228000 | available | Beta |
4 | 2022-10-06 16:28:03.195000 | charging | Beta |
获得预期的结果:
box_id | status |
---|---|
Alpha | Charging |
Beta | Available |
我有以下代码
SELECT IF(连接器_状态.status = 'Charging','Charging', IF(连接器_状态.status ='Available','Not Occupied', IF(连接器_状态.status = 'Faulted','Faulted','Occupied'))) AS group_status, 连接器.连接器_id, 连接器.box_id, status_timestamp FROM 连接器_状态 JOIN 连接器 ON 连接器_状态.连接器 = 连接器.连接器 GROUP BY 连接器.box_id ORDER BY 连接器.box_id
不过,我不知道如何对box_id执行联接以获得最大时间戳.
What is confusing me is if i firstly get the latest timestamp from the 连接器_状态 table and then try and join by box_id, how can i be sure that it will take the latest 连接器 timestanmp for that box_id