如何查询SQL表中相同test_subject_id且date_created差在3分钟内的成对记录

核心逻辑 #

要实现需求，最直接的方案是使用表自连接，通过以下条件过滤符合要求的配对：

• 两条记录的test_subject_id完全一致
• 加入a.id < b.id条件，避免生成「自己和自己配对」的无效结果，同时避免同一对记录重复返回（比如不会同时出现(ID=1,ID=2)和(ID=2,ID=1)两组相同配对）
• 过滤两条记录的date_created时间差不超过3分钟

不同数据库的实现代码 #

MySQL #

SELECT 
    a.id AS record_1_id,
    b.id AS record_2_id,
    a.test_subject_id,
    a.date_created AS record_1_create_time,
    b.date_created AS record_2_create_time
FROM 
    替换为你的实际表名 a
INNER JOIN 
    替换为你的实际表名 b 
    ON a.test_subject_id = b.test_subject_id
    AND a.id < b.id
WHERE 
    TIMESTAMPDIFF(MINUTE, LEAST(a.date_created, b.date_created), GREATEST(a.date_created, b.date_created)) <= 3;

PostgreSQL #

SELECT 
    a.id AS record_1_id,
    b.id AS record_2_id,
    a.test_subject_id,
    a.date_created AS record_1_create_time,
    b.date_created AS record_2_create_time
FROM 
    替换为你的实际表名 a
INNER JOIN 
    替换为你的实际表名 b 
    ON a.test_subject_id = b.test_subject_id
    AND a.id < b.id
WHERE 
    ABS(EXTRACT(EPOCH FROM (a.date_created - b.date_created)) / 60) <= 3;

SQL Server #

SELECT 
    a.id AS record_1_id,
    b.id AS record_2_id,
    a.test_subject_id,
    a.date_created AS record_1_create_time,
    b.date_created AS record_2_create_time
FROM 
    替换为你的实际表名 a
INNER JOIN 
    替换为你的实际表名 b 
    ON a.test_subject_id = b.test_subject_id
    AND a.id < b.id
WHERE 
    DATEDIFF(MINUTE, a.date_created, b.date_created) BETWEEN -3 AND 3;

性能优化建议 #

你的表只有14000条记录，本身量级很小，要进一步加快查询速度，可以给test_subject_id和date_created建立联合索引，能降低自连接的查询耗时。
如果不需要去重配对，可以删掉a.id < b.id条件，不过会返回两倍的配对结果，同时包含自匹配的无效记录。 #

内容的提问来源于stack exchange，提问作者Haseeb Mazhar Ranga