SQL 查询的问题

发布于 2024-12-21 12:12:09 字数 1263 浏览 0 评论 0原文

我需要有关 sql 表的帮助。

该表包含以下列:

EventID、PersonID、EventType、EventTime(datetime) 和其他一些不太重要的列。

假设两个主要事件类型是开门和关门。

我需要同一个人打开一扇门和关上一扇门或者他打开一扇门和打开另一扇门之间的持续时间(以秒为单位)。(这当然允许简单地打开门的序列)

只是为了弄清楚第一个人是否打开了一扇门第二个人关上了门,查询中不应返回任何行。

我希望它高效,但这不是必须的。

我正在使用 2008 SQL microsoft 服务器(SQLEXPRESS)

这是一个表的示例:

EventID | PersonID | EventType | EventDate                | PreviousDoor | CurrentDoor
   1    |    1     |    1      | 12/10/2010 12:00:01.024  |      0       |     23
   2    |    1     |    2      | 12/10/2010 12:05:40.758  |      23      |     0
   3    |    2     |    1      | 12/10/2010 12:12:05.347  |      0       |     12
   4    |    1     |    1      | 12/10/2010 12:50:12.142  |      0       |     23
   5    |    2     |    2      | 12/10/2010 13:00:06.468  |      12      |     23
   6    |    3     |    1      | 13/10/2010 13:00:06.468  |      0       |     23

EventType:1(开门),2(关门)

结果应该是:

EventID | PersonID | EventType | EventDate                | SecondsDifference
   1    |    1     |    1      | 12/10/2010 12:00:01.024  | 339
   3    |    2     |    1      | 12/10/2010 12:12:05.347  | 2881

我真的需要你们的帮助。

提前致谢。

I need help with an sql table.

The table contains the following columns:

EventID, PersonID, EventType, EventTime(datetime), and a few other less significant columns.

Lets say that the two main event types are opening a door and closing a door.

I need the time duration(in seconds) between the same person opening a door and closing it or him opening a door and opening another one.(this of course allows sequences of simply opening doors)

Just to be clear if person 1 opened a door and person 2 closed a door no rows should be returned from the query.

I would like for it to be efficient but that isn't a must.

I'm using the 2008 SQL microsoft server(SQLEXPRESS)

Here is an example of a table:

EventID | PersonID | EventType | EventDate                | PreviousDoor | CurrentDoor
   1    |    1     |    1      | 12/10/2010 12:00:01.024  |      0       |     23
   2    |    1     |    2      | 12/10/2010 12:05:40.758  |      23      |     0
   3    |    2     |    1      | 12/10/2010 12:12:05.347  |      0       |     12
   4    |    1     |    1      | 12/10/2010 12:50:12.142  |      0       |     23
   5    |    2     |    2      | 12/10/2010 13:00:06.468  |      12      |     23
   6    |    3     |    1      | 13/10/2010 13:00:06.468  |      0       |     23

EventType: 1(Opened door), 2(Closed door)

Result should be:

EventID | PersonID | EventType | EventDate                | SecondsDifference
   1    |    1     |    1      | 12/10/2010 12:00:01.024  | 339
   3    |    2     |    1      | 12/10/2010 12:12:05.347  | 2881

I could really use your guys help.

Thanks in advance.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(3

╭⌒浅淡时光〆 2024-12-28 12:12:09

我认为应该这样做:

    SELECT          p1.EventID,
                    p1.PersonID,
                    p1.EventType,
                    p1.EventDate,
                    DATEDIFF(SECOND, p1.EventDate, p2.EventDate) AS SecondsDifference
    FROM            [Event] p1
    LEFT JOIN       [Event] p2  --Left join to self returning only closed door events
    ON              p2.PersonID = p1.PersonID
    AND             p2.EventType = 2 -- Closed Door
    AND             p1.EventID < p2.EventID --We don't want to bring back events that happened before the next event
    WHERE           p2.EventID IS NOT NULL --We don't want to show any people that have not closed a door

I think this should do it:

    SELECT          p1.EventID,
                    p1.PersonID,
                    p1.EventType,
                    p1.EventDate,
                    DATEDIFF(SECOND, p1.EventDate, p2.EventDate) AS SecondsDifference
    FROM            [Event] p1
    LEFT JOIN       [Event] p2  --Left join to self returning only closed door events
    ON              p2.PersonID = p1.PersonID
    AND             p2.EventType = 2 -- Closed Door
    AND             p1.EventID < p2.EventID --We don't want to bring back events that happened before the next event
    WHERE           p2.EventID IS NOT NULL --We don't want to show any people that have not closed a door

尝试这样的事情:

select t1.EventID, t1.PersonID, t1.EventType, t1.EventDate, datediff(second, t1.EventDate, t2.EventDate) as 'SecondsDifference'
from [Event] t1
inner join [Event] t2 on t2.PersonID = t1.PersonID and t2.EventType = 2 and t2.PreviousDoor = t2.CurrentDoor and t2.EventID < t1.EventID
where t1.EventType = 1

Try something like this:

select t1.EventID, t1.PersonID, t1.EventType, t1.EventDate, datediff(second, t1.EventDate, t2.EventDate) as 'SecondsDifference'
from [Event] t1
inner join [Event] t2 on t2.PersonID = t1.PersonID and t2.EventType = 2 and t2.PreviousDoor = t2.CurrentDoor and t2.EventID < t1.EventID
where t1.EventType = 1
删除→记忆 2024-12-28 12:12:09

使用 ROW_NUMBERPARTITION 有帮助吗?
我不确定以下是否是合法的 SQL 语句,因此请将其视为半伪代码。

SELECT *, 
  ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY EventTime) AS RowNumber,
  datediff(seconds, t2.EventTime, t1.EventTime) AS SecondsDiff
FROM Events t1 
  INNER JOIN 
    SELECT *,
      ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY EventTime) AS RowNumber 
    From Events t2 
  ON t1.RowNumber + 1 = t2.RowNumber 
    AND t1.PersonID = t2.PersonID AND t1.EventType = 1 
    AND (t2.EventType = 1 OR t2.EventType = 2)

Will using ROW_NUMBER and PARTITION help?
I'm not sure if the following is a legal SQL statement so please consider it as semi pseudo code.

SELECT *, 
  ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY EventTime) AS RowNumber,
  datediff(seconds, t2.EventTime, t1.EventTime) AS SecondsDiff
FROM Events t1 
  INNER JOIN 
    SELECT *,
      ROW_NUMBER() OVER(PARTITION BY PersonID ORDER BY EventTime) AS RowNumber 
    From Events t2 
  ON t1.RowNumber + 1 = t2.RowNumber 
    AND t1.PersonID = t2.PersonID AND t1.EventType = 1 
    AND (t2.EventType = 1 OR t2.EventType = 2)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文