Sql日期选择彼此在X秒范围内的行

My sql table is something like (message,created)

我的sql表就像(消息,创建)

I want to select those rows which are within X seconds from each other. Say the last message is within X seconds from NOW(), then it should select it. If the second last message is within X seconds from the last message then it should select it too. In other words each row should be compared with the next row and checked. For the last row it should be check with say NOW(). Basically I want the last session of messages (i.e. last set of messages that we linked to each other, assuming that consequtive messages within X seconds are linked to each other)

我想选择彼此相距X秒的那些行。假设最后一条消息在NOW()的X秒内,然后它应该选择它。如果倒数第二条消息在距离最后一条消息的X秒内,则它也应该选择它。换句话说,每行应与下一行进行比较并进行检查。对于最后一行,应该用NOW()来检查。基本上我想要最后一段消息(即我们彼此链接的最后一组消息,假设X秒内的连续消息彼此链接)

I have no idea how to go about writing an SQL query for this. Is it even possible?

我不知道如何为此编写SQL查询。它甚至可能吗?

Thank you very much for your time.

非常感谢您的宝贵时间。

2 个解决方案

#1

This script works in SQLServer. You should be able to take out the select statement and run it in MySQL.

此脚本适用于SQLServer。您应该能够取出select语句并在MySQL中运行它。

DECLARE @Messages TABLE (Message VARCHAR(10), Created DATETIME)
DECLARE @Interval FLOAT

-- Interval is 1 day.
SET @Interval = 1

-- These should be in result
INSERT INTO @Messages VALUES ('Message1', GetDate())    
INSERT INTO @Messages VALUES ('Message2', GetDate()-1)
-- These should not be in result
INSERT INTO @Messages VALUES ('Message3', GetDate()-3)
INSERT INTO @Messages VALUES ('Message4', GetDate()-5)

SELECT m1.Message, m1.Created
FROM @Messages m1
     INNER JOIN @Messages m2 ON m2.Created <= m1.Created + @Interval                                
                                AND m2.Created >= m1.Created
                                AND m2.Message <> m1.Message
UNION ALL SELECT m2.Message, m2.Created
FROM @Messages m1
     INNER JOIN @Messages m2 ON m2.Created <= m1.Created + @Interval                                
                                AND m2.Created >= m1.Created
                                AND m2.Message <> m1.Message
ORDER BY Created

#2

I believe you are thinking too complicated (But then again, maybe I misunderstood the requirement?)

我相信你的想法太复杂了(但话又说回来,也许我误解了这个要求?)

This selects all messages created within 30 seconds before a particular message:

这将选择在特定消息之前30秒内创建的所有消息:

SELECT
  Id,
  MessageText,
  MessageDate
FROM
  Message
WHERE
  TIME_TO_SEC(TIMEDIFF(
    (
      SELECT MessageDate 
      FROM   Message
      WHERE  Id = 17
    ),
    MessageDate
  )) <= 30

where 17 is of course the message Id you are interested in, and 30 is the number of seconds in your time frame.

其中17当然是您感兴趣的消息ID,30是您的时间范围内的秒数。

#1