SQL 查询的问题
我需要有关 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我认为应该这样做:
I think this should do it:
尝试这样的事情:
Try something like this:
使用
ROW_NUMBER
和PARTITION
有帮助吗?我不确定以下是否是合法的 SQL 语句,因此请将其视为半伪代码。
Will using
ROW_NUMBER
andPARTITION
help?I'm not sure if the following is a legal SQL statement so please consider it as semi pseudo code.