如何（通常）计数重复行（并删除重复项）？ [英] How to (generically) count duplicate rows (and remove duplicates)?

问题描述

是否有一个通用的SELECT语句来检测重复行（相同，其中所有列都相等）？ E.G，列2& 4在下表中

  titel |解释| jahr 
 ----------------------------------- + --------- --------- + ----- 
美容|坂本隆一| 1990 
再见国家（Hello Nightclub）| Groove Armada | 2001 
 Glee | Bran Van 3000 | 1997 
再见国家（Hello Nightclub）| Groove Armada | 2001 
  

还是需要特定于表的SELECT？

有人给了我一个带有多个表的Sqlite d / b，每个表都有多个相同的行（每个表中有不同的列），所以我更喜欢一个通用的解决方案。 p>

之后，我必须弄清楚如何删除重复项。也许我可以在SELECT上使用DISTINCT，存储在临时表中，删除原始文件并重命名临时表？

解决方案

你一直有正确的想法。您必须在每个表格上运行以下内容：

  select table titel，interpret，jahr from table1 
  

您可以将不同的行转储到另一个表中，如下所示：

 创建表table2作为
选择不同的titel，解释，jahr从table1 
  

然后你可以像这样放下这个简单的表：

  drop table table1 
  

将新创建的表重命名为table1，如下所示：

 code> alter table table2重命名为table1 
  

查找表中每条记录的行号：

  select a.rowid，a。* from table1 a 
  

要查找只有重复记录的行号：

  select a.rowid，a。* 
 from table1 a 
 inner join 
（
 select titel，interpret，jahr 
 from table1 
 group by titel，explain，jahr 
 have count（*）> 1 
）b on 
 a.titel = b.titel 
和a.interpret = b.interpret 
和a.jahr = b.jahr 
  




Is there a generic SELECT statement to detect duplicate rows ("identical", where all columns are equal)? E.G, columns 2 & 4 in the following table

 titel                             | interpret        | jahr
-----------------------------------+------------------+-----
 Beauty                            | Ryuichi Sakamoto | 1990
 Goodbye Country (Hello Nightclub) | Groove Armada    | 2001
 Glee                              | Bran Van 3000    | 1997
 Goodbye Country (Hello  Nightclub)| Groove Armada    | 2001

Or do I need a SELECT which is specific to the table?

Someone has given me an Sqlite d/b with multiple tables each of which look like like they have multiple identical rows (with different columns in each table), so I would prefer a generic solution.

After that, I have to figure out how to delete the duplicates. Maybe I can use DISTINCT on the SELECT, store in a temp table, delete the original and rename the temp table?

解决方案

You had the right idea all along. You will have to run the following on each table:

select distinct titel, interpret, jahr from table1

You could dump the distinct rows into another table like so:

create table table2 as
select distinct titel, interpret, jahr from table1

You can then drop the intial table like so:

drop table table1

Rename the newly created table to table1 like so:

alter table table2 rename to table1

To find row number of each record in the table:

select a.rowid, a.* from table1 a

To find row number of only the records that are duplicate:

select a.rowid, a.* 
from table1 a 
inner join 
(
      select titel, interpret, jahr 
      from table1 
      group by titel, interpret, jahr 
      having count(*) > 1
) b on 
      a.titel = b.titel 
      and a.interpret = b.interpret 
      and a.jahr = b.jahr

这篇关于如何（通常）计数重复行（并删除重复项）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！