需要一些简单的SQL查询帮助

I've got a game which dumps state information to a database. Eg. Connected, Lost Connection, Grabbed Screen Dump, etc.

我有一个将状态信息转储到数据库的游戏。例如。连接,丢失连接,抓取屏幕转储等

Each state is defined by a field called StateTypeId, which is a foreign key to a simple lookup table 'StateTypes'.

每个状态由一个名为StateTypeId的字段定义,该字段是简单查找表“StateTypes”的外键。

Now, i'm trying to find all the people who are currently 'Connected' (eg. StateTypeId == 1), and i'm not sure how to do it. When i say currently connected, I mean that a user doesn't have a 'LostConnection' state (ie. StateType == 2) as their most recent entry.

现在,我正在尝试找到当前“已连接”的所有人(例如StateTypeId == 1),我不知道该怎么做。当我说当前连接时,我的意思是用户没有'LostConnection'状态(即StateType == 2)作为他们最近的条目。

I was thinking along the lines of.

我正在思考。

SELECT UserId
FROM UserData
WHERE StateType = 1
GROUP BY UserId

SELECT UserId
FROM UserData
WHERE StateType != 1
GROUP BY UserId

but this returns all the results, not just the most recent stuff.

但这会返回所有结果,而不仅仅是最新的结果。

Any suggestions?

(i'm guessing I'm missing a TOP(1) and some rank/order by stuff?)

(我猜我错过了TOP(1)和一些排名/顺序?)

EDIT

DOH! i forgot to mention, i DO have a DateTime column in the table -> DateEntered.

DOH!我忘了提,我在表中有一个DateTime列 - > DateEntered。

EDIT 2 - Clarification of question AND some sample data.

I've tried the suggested solutions and i'm not having much luck. As such, i'll provide some sample data to see if this helps clarify my question.

我尝试了建议的解决方案,但我没有太多运气。因此,我将提供一些示例数据,看看这是否有助于澄清我的问题。

ID State          UserName DateAdded
1  Connected      Foo      1/1/2000 12:00 
2  Connected      Bar      1/1/2000 12:01  
3  ScreenShot     Foo      1/1/2000 12:05 
4  LostConnection Foo      1/1/2000 12:06
5  Connected      Joe      1/1/2000 12:10
6  LostConnection Joe      1/1/2000 12:15
7  Screenshot     Bar      1/1/2000 12:16
8  Connected      Jane     1/1/2000 12:22

So looking at this, the people who are connected (currently) are Bar and Jane. Foo and Joe are gone. Also note, Bar's last activity a screenshot, meaning, his last activity was not a LostConnection state.

所以看看这个,连接(目前)的人是Bar和Jane。 Foo和Joe走了。还要注意,Bar的最后一个活动是截图,意思是,他的最后一个活动不是LostConnection状态。

HTH.

6 个解决方案

#1

What you are looking for is the "most recent" entry having a StateType of 1 or 2:

您正在寻找的是StateType为1或2的“最新”条目:

Then you are looking to identify the "last" one for each user:

然后,您希望为每个用户标识“最后一个”:

select UserName, max(DateAdded) as LastDate
from UserData
where State IN ('Connected', 'LostConnection')
Group by UserName

Then you need to check the state of these (which requires a join with the original table):

然后你需要检查这些状态(这需要与原始表连接):

SELECT UserName
FROM UserData allData join 
    (select UserName, max(DateAdded) as LastDate
    from UserData
    where State IN ('Connected', 'LostConnection')
    Group by UserName) as lastData on allData.UserName = lastData.UserName
        and allData.DateAdded = lastData.LastDate
WHERE StateType = 'Connected'

Another way to join would use the id of the UserData like this:

另一种加入方式是使用UserData的id,如下所示:

SELECT UserName
FROM UserData allData join 
    (select max(id) as id -- if ids are assigned in ascending order
    from UserData
    where State IN ('Connected', 'LostConnection')
    Group by UserName) as lastData on allData.id = lastData.id
WHERE StateType = 'Connected'

#2

if you have a datetime entry or time stamp you can do it this way:

如果您有日期时间条目或时间戳,您可以这样做:

Select UserID
From UserData
Where StateType = 1
Group by UserID
Having Date = Max(Date)

#3

I think it would be worth testing the performance of a NOT EXISTS approach.

我认为值得测试NOT EXISTS方法的性能。

i.e. find users with the appropriate StateType which do not have a more recent record.

即查找具有适当StateType且没有更新记录的用户。

SELECT UD1.UserId
FROM  UserData AS UD1
WHERE UD1.StateType = 1
      AND NOT EXISTS
      (
--        Check for newer records:
          SELECT *
          FROM UserData AS UD2
          WHERE UD2.DateEntered > UD1.DateEntered
      )

If their are many rows stored in UserData, and/or you keep them for long periods of time, then this approach will probably perform badly.

如果它们存储在UserData中的行数很多,和/或您长时间保留它们,那么这种方法可能会表现不佳。

If that is the case, and if this is a frequently occurring query, I would create a UserCurrentState table (columns = UserId, StateType) which is updated when StateType changes (I would put a Trigger on the UserData table to enforce this)

如果是这种情况,如果这是一个经常发生的查询,我会创建一个UserCurrentState表(columns = UserId,StateType),当StateType更改时会更新(我会在UserData表上放置一个Trigger来强制执行此操作)

StateType in UserData is not going to index well enough to be useful. You could have an index with DateEntered, StateType, UserId which would cover the query and should therefore be used, but that will only be helpful, in practice, if you can restrict the range of DateEntered that is checked. If you are only looking for the last hour / day fine, if you are looking for since-the-beginning-of-time then it won't help much.

UserData中的StateType不会很好地编入索引以使其有用。您可以使用DateEntered,StateType,UserId来覆盖查询,因此应该使用该索引,但实际上,如果您可以限制检查的DateEntered范围,那么这只会有所帮助。如果您只是在寻找最后一小时/天的罚款,如果您从一开始就在寻找,那么它将无济于事。

#4

Actually, this may be more efficient (SQL Server 2005 onwards)

实际上,这可能更有效(SQL Server 2005以上)

SELECT UserId
FROM
(
    SELECT  *, row_number() 
        OVER
        (
            partition BY UserId 
            ORDER BY DateEntered DESC
        ) AS RecordNumber
    FROM UserData
) AS UD
WHERE      RecordNumber = 1
        AND StateType = 1

#5

Here's another possible solution, though the solutions using NOT EXISTS probably perform better.

这是另一种可能的解决方案,尽管使用NOT EXISTS的解决方案可能表现更好。

SELECT U1.*
FROM UserData U1
 LEFT OUTER JOIN UserData U2
 ON (U1.UserName = U2.UserName
  AND U1.DateAdded < U2.DateAdded
  AND U2.StateType IN (1,2))
WHERE U1.StateType = 1
  AND U2.UserName IS NULL
ORDER BY U1.DateAdded DESC;

#6

You will need a timestamp column and you'll need to write a query that joins the table to itself like this

您将需要一个时间戳列,您需要编写一个将表连接到自身的查询

Select *
From Userdata U
Where 1 = (Select Top 1 StateType
           From Userdata U2
           Where U.UserId = U2.UserId
           order by u2.TimeStamp desc)

#1