mysql - 从 MYSQL 中删除数据

Question

我有一个表，其中包含 MySQL 中的数据，如下所示。 Size 显示文件大小(以 MB 为单位)。

--------------------------------------------
| id  | name | size | user_id | created_at |
--------------------------------------------
| 1   | a    | 120  | 1       | 2017-04-03 |
--------------------------------------------
| 2   | b    | 280  | 1       | 2017-04-04 |
--------------------------------------------
| 3   | c    | 220  | 1       | 2017-04-05 |
--------------------------------------------

现在，如果特定用户超过 500MB 的限制，我想删除最旧的数据。在上述情况下，ID#1 需要删除。

有人可以帮我写一个查询吗？

Answer 1

您可以通过三个步骤为此构建一个查询。首先，您会获取每个用户在 500MB 阈值之后加载文件的日期:

select  t1.user_id, t1.created_at
from    files t1
join    files t2
on      t1.user_id = t2.user_id and
        t2.created_at > t1.created_at
group by t1.user_id, t1.created_at
having  sum(t2.size) >= 500

然后，对于每个用户，您都会获得这些日期中较高的日期

select  user_id, max(created_at)
from    (
            select  t1.user_id, t1.created_at
            from    files t1
            join    files t2
            on      t1.user_id = t2.user_id and
                    t2.created_at > t1.created_at
            group by t1.user_id, t1.created_at
            having  sum(t2.size) >= 500
        )

最后，通过将其与源表连接起来，将其用作实际删除的引用:

delete t1
from   yourTable t1
join   (
            select  user_id, max(created_at) as created_at
            from    (
                        select  t1.user_id, t1.created_at
                        from    files t1
                        join    files t2
                        on      t1.user_id = t2.user_id and
                                t2.created_at > t1.created_at
                        group by t1.user_id, t1.created_at
                        having  sum(t2.size) >= 500
                    )
       ) t2
on     t1.user_id = t2.user_id and
       t1.created_at = t2.created_at

编辑

Working Rextester