在MySQL中删除重复项 [英] Deleting Duplicates in MySQL

问题描述

我有一张这样的桌子

userid  visitorid   time
1       10          2009-12-23
1       18          2009-12-06
1       18          2009-12-14
1       18          2009-12-18
1705    1678        2010-01-24
1705    1699        2010-01-24
1705    1700        2010-01-24
1712    1           2010-01-25
1712    640         2010-01-24
1712    925         2010-01-25
1712    1600        2010-01-24
1712    1630        2010-01-25
1712    1630        2010-01-24
1713    1           2010-01-24
1713    1           2010-01-23

我想执行一次查询，以便删除除最新重复项之外的所有重复项.我希望你有个主意吗?

I would like to perform a query such that it removes all the duplicates except for the latest one. I Hope you get an idea?

例如，查询后表格必须是这样

Example, after the query the table must be like this

userid  visitorid   time
1       10          2009-12-23
1       18          2009-12-18
1705    1678        2010-01-24
1705    1699        2010-01-24
1705    1700        2010-01-24
1712    1           2010-01-25
1712    640         2010-01-24
1712    925         2010-01-25
1712    1600        2010-01-24
1712    1630        2010-01-25
1713    1           2010-01-24

推荐答案

Delete from YourTable VersionA
  where VersionA.Time NOT IN
    ( select MAX( VersionB.Time ) Time
         from YourTable VersionB
         where VersionA.UserID = VersionB.UserID
           and VersionA.VisitorID = VersionB.VisitorID )

语法可能需要调整，但是应该做到这一点.此外，您可能希望将子查询预查询到其自己的表FIRST中，然后对该结果集运行DELETE FROM.

Syntax might need to be adjusted, but SHOULD do the trick. Additionally, you may want to pre-query the Subselect into its own table FIRST, then run the DELETE FROM against that result set.

这篇关于在MySQL中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！