mysql删除重复行+排序条件

Question

我试图从我的 Mysql 表中删除一些行，当一个键重复时(这里是“url”)并保留一个特定的行(这里是最小的“key1”和“key2”)

示例:

Table t1
Id    Url    Key1    Key2
1     A.com   10      10
2     B.com   20      25
3     B.com   21      25
4     C.com   35      35
5     C.com   35      37

期望的输出是:

Table t1
Id    Url    Key1    Key2
1     A.com   10      10
3     B.com   21      25
5     C.com   35      37

所以查询(如果存在的话)应该是这样的:

• 选择 Url 重复的行
• 然后按Key1排序，去掉Key1严格劣后的行
• 如果Key1相等，则去掉Key2次的行

谢谢

Answer 1

您想保留 key1、key2 最大的行。一种简单的表达方式是:

delete t1 from table t1
    where exists (select 1
                  from t1 t11
                  where t11.url = t1.url and
                        (t11.key1 > t1.key1 or
                         t11.key1 = t1.key1 and t11.key2 > t1.key2
                        )
                 );

唉，MySQL 不允许这种构造，因为你正在使用被删除的表。所以，你可以这样做:

delete t1
    from t1 left join
         (select t.*,
                 (select max(key2)
                  from t1
                  where t1.url = t.url and t1.key = t.maxkey1
                 ) as maxkey2
          from (select url, max(key1) as maxkey1
                from t1
                group by url
               ) t
        ) t
        on t1.url = t.url and t1.key1 = t.maxkey1 and t2.key2 = t.maxkey2
    where t.url is null;