同一个表上的两个查询显示具有不同值的同一行？

I'm new to SQL, but I'm pretty sure something strange is happening here. I have a MySQL database with a table (eventtable) with 4 columns (RoomNum, EventName, EventStatus, EventDateTime).

我是SQL的新手,但我很确定这里发生了一些奇怪的事情。我有一个MySQL数据库,其中包含一个包含4列(RoomNum,EventName,EventStatus,EventDateTime)的表(eventtable)。

Certain EventNames repeat with the EventStatus toggling ON and OFF at different times. I'm trying to get the latest entry so that I have the "current" state, so I use:

某些EventNames重复使用EventStatus在不同时间切换ON和OFF。我正在尝试获取最新条目以便我具有“当前”状态,因此我使用:

select RoomNum, EventName, EventStatus, max(EventDateTime) as MaxDate
    from eventtable where eventName = "DND"
    group by RoomNum

Resulting in: 同一个表上的两个查询显示具有不同值的同一行？

Just to make sure everything is ok, I check each room to make sure it is giving the latest one with:

为了确保一切正常,我会检查每个房间,以确保它提供最新的房间:

SELECT * FROM `eventtable`
where RoomNum = "070#" and eventname = "DND" /*where "#" is a specific digit*/
order by eventdatetime asc

The first one looked good: 同一个表上的两个查询显示具有不同值的同一行？

第一个看起来不错:

But then the others don't! (The EventStatus is wrong even though the time is correct): 同一个表上的两个查询显示具有不同值的同一行？

但其他人却没有! (即使时间正确,EventStatus也是错误的):

what I don't get is that EventDateTime matches which tells me I'm looking at the same row of data, but then how can the internal value (EventStatus) be different?

我没有得到的是EventDateTime匹配告诉我我正在查看同一行数据,但那么内部值(EventStatus)如何不同?

Please help. Thank you.

请帮忙。谢谢。

2 个解决方案

#1

By using below query, You can get the result as per your requirement. Please try.

通过使用以下查询,您可以根据您的要求获得结果。请试试。

select * from(select RoomNum, EventName, EventStatus, EventDateTime from eventtable where eventName = "DND" order by EventDateTime desc)tab group by RoomNum ;

select * from(选择RoomNum,EventName,EventStatus,事件表中的EventDateTime,其中eventName =“DND”按EventDateTime desc排序)选项卡组由RoomNum;

#2

This is why firebird does not allow this. Only this is valid

这就是firebird不允许这样做的原因。只有这是有效的

select RoomNum, max(EventDateTime) as MaxDate
from eventtable where eventName = "DND"
group by RoomNum

EventName and EventStatus values are arbitrary. You have not told it which ones you want. And I don't think you can. RoomNumn is implicit because of "group by".

EventName和EventStatus值是任意的。你还没告诉它你想要哪一个。我认为你不能。由于“分组依据”,RoomNumn是隐含的。

To get the data you want, one way would be to use a subselect where the max(EventDateTime) returns a reference.

要获得所需的数据,一种方法是使用max(EventDateTime)返回引用的子选择。

#1