在 SQL 中抓取下一行

发布于 2024-09-13 02:11:51 字数 351 浏览 2 评论 0原文

我正在尝试从该数据库的下一行获取一个字段。我可以使用登录时间来确定注销时间，但我还想获取下一次登录时间，以便我也可以确定注销[注销]时间。有什么建议吗？谢谢

|-LogonDate---|-PersonID-|-LoggedOnTime---|   
|5/30 12:00pm |100       |3600            | 
|5/30 01:00pm |100       |3430            |
|5/30 03:30pm |100       |3000            |

SELECT PersonID, LogonDate, LoggedOnTime
FROM Agent_Logout

原文

I am trying to grab a field from the next row of this database. I can use the logged on time to determine the logout time but I would like to also grab the next login time so I can determine the logout [logged off] time as well. Any suggestions? Thank you

|-LogonDate---|-PersonID-|-LoggedOnTime---|   
|5/30 12:00pm |100       |3600            | 
|5/30 01:00pm |100       |3430            |
|5/30 03:30pm |100       |3000            |

SELECT PersonID, LogonDate, LoggedOnTime
FROM Agent_Logout

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

妞丶爷亲个 2024-09-20 02:11:52

可以通过分析函数来实现。不知道你使用哪个 DBMS，但 Oracle 和我认为 SQL Server 也可以做到。我在Oracle里做过一次。有一个很好的方面解释它这里当我需要这个分析函数时我总是去那里（通常相当排名）。您将需要一个名为 LEAD 的函数。我认为它会像这样工作：

SELECT PersonID, LogonDate, LoggedOnTime
       LEAD(LoggedOnTime, 1, 0) OVER (PARTITION BY PersonID ORDER BY LogonDate, LoggedOnTime)
FROM Agent_Logout

如果这不能按您想要的方式工作，请告诉我（因为我还没有测试过它）。然后我会努力让它发挥作用。但我非常有信心......如果您的数据库允许语法。

It is possible with a analytic function. Don't know which DBMS you use but Oracle and I think also SQL Server can do it. I did it once in Oracle. There is an excellent side explaining it here I always go there when I need this analytic functions (Usually is rather RANK). Your will need a function called LEAD. I think it would work like this:

SELECT PersonID, LogonDate, LoggedOnTime
       LEAD(LoggedOnTime, 1, 0) OVER (PARTITION BY PersonID ORDER BY LogonDate, LoggedOnTime)
FROM Agent_Logout

Let me know if this does not work as you want (Because I haven't tested it). I will then try to go and make it work. But i am very confident ... if your database allows the syntax.

回复收藏 0 原文

帝王念 2024-09-20 02:11:51

您可以使用子选择来获取下一个 LogonDate，然后确定它们之间的差异。

SELECT
    a.PersonID,
    a.LogonDate,
    a.LoggedOnTime,
    (SELECT b.LogonDate
     FROM Agent_Logout AS b
     WHERE b.LogonDate > a.LogonDate
     ORDER BY b.LogonDate ASC
     LIMIT 1) AS NextLogonDate
FROM Agent_Logout AS a

如果使用 MS SQL Server，请删除 LIMIT 1 并在此处添加 TOP 1：SELECT TOP 1 b.LogonDate

编辑： 感谢 @JYelton 指出 MS SQL Server 的 TOP 1 和 ORDER BY b.LogonDate ASC

You could use a sub-select to get the next LogonDate, then determine the difference between them.

SELECT
    a.PersonID,
    a.LogonDate,
    a.LoggedOnTime,
    (SELECT b.LogonDate
     FROM Agent_Logout AS b
     WHERE b.LogonDate > a.LogonDate
     ORDER BY b.LogonDate ASC
     LIMIT 1) AS NextLogonDate
FROM Agent_Logout AS a

If using MS SQL Server, remove LIMIT 1 and add TOP 1 here: SELECT TOP 1 b.LogonDate

EDIT: Thanks to @JYelton for pointing out TOP 1 for MS SQL Server, and ORDER BY b.LogonDate ASC

回复收藏 0 原文

~没有更多了~