如何找到2列中的重复项不是1 [英] How to find duplicates in 2 columns not 1

问题描述

我有一个MySQL数据库表，其中有两列让我感兴趣。单独地，他们可以每个都有重复的，但是它们不应该有两个具有相同值的两个副本。

stone_id 可以有重复，只要每个 upsharge 标题是不同的，反之亦然。但是例如 stone_id = 412和 upcharge_title =蓝宝石，该组合应该只发生一次。

这是可以的：

  stone_id = 412 upcharge_title =蓝宝石
 stone_id = 412 upcharge_title =ruby
  

这不行：

  stone_id = 412 upcharge_title =蓝宝石
 stone_id = 412 upcharge_title =蓝宝石
  pre> 
 
 
是否有一个查询将在两个字段中找到重复项？如果可能的话，我有一种方法来设置我的数据库不允许？
 
 
 
我正在使用MySQL版本4.1.22 
解决方案
您应该在两个字段之间设置一个复合键。这将需要每行唯一的stone_id和upcharge_title。
 
 
 
要找到现有的重复项，请尝试以下操作：
  select stone_id，
 upcharge_title，
 count（*）
 from your_table 
 group by stone_id，
 upcharge_title 
有count（*）> 1 
  
 
I have a MySQL database table with two columns that interest me. Individually they can each have duplicates, but they should never have a duplicate of BOTH of them having the same value.

stone_id can have duplicates as long as for each upsharge title is different, and in reverse. But say for example stone_id = 412 and upcharge_title = "sapphire" that combination should only occur once.

This is ok:
stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "ruby"
This is NOT ok:
stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "sapphire"
Is there a query that will find duplicates in both fields? And if possible is there a way to set my data-base to not allow that?

I am using MySQL version 4.1.22
解决方案
You should set up a composite key between the two fields.  This will require a unique stone_id and upcharge_title for each row.

As far as finding the existing duplicates try this:
select   stone_id,
         upcharge_title,
         count(*)
from     your_table
group by stone_id,
         upcharge_title
having   count(*) > 1


                        
这篇关于如何找到2列中的重复项不是1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！