mysql - SQL 查询删除字符串结尾值相同的两行

Question

我有一个表，它有 4 列(id、name、value、group)和 ~ 150,000 行。

我需要删除所有出现的任何一组两行，其中 name 列值以相同的字符串(一个 uuid)和 value 列值结尾第二行在指定日期之前。

name 列值始终以两个字符串之一开头:st1_ 始终在 value 列中包含 json 数据，st2_ 在 value 列中始终有一个 unix 时间戳。

这是一个示例集(group 列已删除，因为它无关紧要):

╔════╦═══════════╦════════════╗
║ id ║   name    ║   value    ║
╠════╬═══════════╬════════════╣
║ 1  ║ st1_uuid1 ║ {json}     ║
║ 2  ║ st2_uuid1 ║ 1448880401 ║
║ 3  ║ st1_uuid2 ║ {json}     ║          
║ 4  ║ st2_uuid2 ║ 1448880414 ║
║ 5  ║ st1_uuid3 ║ {json}     ║    
║ 6  ║ st2_uuid3 ║ 1448880468 ║
╚════╩═══════════╩════════════╝

在此示例中，我如何编写查询以根据 unix 时间戳删除第 1-4 行？

SQLFiddle

Answer 1

假设在确定重复项时应将多次出现的 uuid 值全部考虑在一起，则以下查询应该有效。

DELETE FROM yourTable
WHERE RIGHT(name, 32) IN (SELECT * 
             FROM (SELECT RIGHT(name, 32) FROM yourTable 
                   GROUP BY RIGHT(name, 32)
                   HAVING COUNT(*) > 1 AND
                   MIN(CASE WHEN value REGEXP '[0-9]+' THEN CAST(value AS UNSIGNED) ELSE ~0 END) < someValue
                  ) AS t
            )

这是 SQL Fiddle 演示 GROUP BY 查询正确地识别了记录中的 uuids 对于给定时间戳 someValue 应该删除的记录:

SQLFiddle