如何查找/删除同一行中的重复记录 [英] How to find/delete duplicated records in the same row

问题描述

可以进行查询以查看同一行中是否存在重复的记录？

我试图找到一个解决方案，但我可以找到的是检测列中的重复字段，而不是行。

例如，假设我有一个包含行和项目的表：

  id | item1 | item2 | item3 | item4 | item5 | upvotes | downvotes | 
 ---------------------------------------------- ---------------------- 
 | 1 |红色|蓝色|红色|黑色|白| 12 | 5 | 
  

所以我想看看是否可以进行查询以检测具有相同记录的字段在这种情况下为红色）并删除它们，和/或如何重新设计我的结构以不允许重复。

谢谢。

解决方案

重新设计：

您可以将布局重新设计为：

  | id |项目| 
  

使用（id，item）作为主键，这将禁止两次相同的项目对于给定的 id 。

所以数据将如下所示：

  | id |项目| 
 | 1 |红色| 
 | 1 |蓝色| 
 ... 
  

试图插入一个 1 |红色| 再次会抛出错误。

没有重新设计：

如果你不想更改布局，此查询将找到 fieldX 字段之一等于其他 fieldX 同一行：

  SELECT t.id FROM（select 1）dummy 
 JOIN（SELECT id，item1作为项目FROM表
 UNION SELECT id，item2作为项目FROM表
 UNION SELECT id，item3作为项目FROM表
 UNION SELECT id，item4作为项目FROM表
 UNION SELECT id ，item5作为FROM表）t 
 GROUP BY t.id 
 HAVING count（*）< 5 
  

（假设你有5个 fieldX 列。）

这实际上计算了每个 id 的不同 fieldX code>。如果计数与字段数不同，则有重复。查询返回重复的行的ids。

测试后，您可以删除

  DELETE FROM table WHERE id IN（<以前的查询此处>）
  

It's possible to make a query to see if there is duplicated records in the same row?

I tried to find a solution but all I can find is to detected duplicated fields in columns, not in rows.

example, let's say I have a table with rows and items:

| id | item1 | item2 | item3 | item4 | item5 | upvotes | downvotes |
--------------------------------------------------------------------
| 1  |  red  | blue  |  red  | black | white |   12    |    5      |

So I want to see if is possible to make a query to detect the fields with the same record (in this case red) and delete them, and/or how to redesign my structure to not allow the duplicates.

Thanks.

解决方案

With redesign:

You may redesign the layout to something like this:

| id | item |

With (id, item) as primary key, this will forbid having twice the same item for a given id.

So the data will looks like this:

| id | item |
| 1  | red  |
| 1  | blue |
...

Trying to insert a | 1 | red | again will throw an error.

Without redesign:

If you don't want to change the layout, this query will find rows in which one of the fieldX field equals an other fieldX of the same row:

SELECT t.id FROM (select 1) dummy
JOIN (SELECT id, item1 as item FROM table
      UNION SELECT id, item2 as item FROM table
      UNION SELECT id, item3 as item FROM table
      UNION SELECT id, item4 as item FROM table
      UNION SELECT id, item5 as item FROM table) t
GROUP BY t.id
HAVING count(*) < 5

(Assuming you have 5 of those fieldX columns.)

This actually counts the different fieldX values for each id. If the count is different than the number of fields, then there is a duplicate. The query returns the ids of the rows in which there is a duplicate.

After testing you can delete rows with

DELETE FROM table WHERE id IN ( <the previous query here> )

这篇关于如何查找/删除同一行中的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！