SQLite如何删除除一行之外的所有重复行? [英] SQLite how to remove all duplicated rows except one?

问题描述

我有一个奇怪的表调用 mytable，没有主列.

I have a weird table call mytable, no column is primary.

  Name    |  Company  |  Position
 Michael     Google      Tester
 Michael     Google      Tester
 Michael     Google      Tester
 Peter       Facebook    Developer
 Peter       Facebook    Developer
 Peter       Facebook    Developer
 Peter       Facebook    Developer

我想要的

  Name    |  Company  |  Position
 Michael     Google      Tester
 Peter       Facebook    Developer 

我在这里找到了一些有同样问题的解决方案，但它们不起作用.例如:DELETE FROM mytable WHERE Name NOT IN (SELECT MAX(Name) FROM mytable GROUP BY Company);

With some solutions I found with the same question here, they did not work. For example: DELETE FROM mytable WHERE Name NOT IN (SELECT MAX(Name) FROM mytable GROUP BY Company);

我应该直接在这个表中编辑，使用 SQLite，不创建新表，也没有 CTE.我该怎么做?

I should edit right in this table, use SQLite, no new table creation and no CTE. How can I do it?

推荐答案

您可以选择保留 rowid 分组的 min 或 max通过显示的 3 列.

You can choose to keep the min or max of rowid grouping by the 3 columns shown.

delete from myTable
where rowid not in (select min(rowid)
                    from myTable
                    group by name,company,position)

这篇关于SQLite如何删除除一行之外的所有重复行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！