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

问题描述

我有一个 MySQL 数据库表，其中包含我感兴趣的两列.单独地，他们每个人都可以有重复项，但他们永远不应该有两个具有相同值的重复项.

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 可以有重复，只要每个 upsharge 的标题不同，并且相反.但是例如 stone_id = 412 和 upcharge_title = "sapphire" 组合应该只出现一次.

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.

没关系:

stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "ruby"

这不行:

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?

我使用的是 MySQL 4.1.22 版

I am using MySQL version 4.1.22

推荐答案

你应该在两个字段之间设置一个复合键.这需要每行都有一个唯一的 stone_id 和 upcharge_title.

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屋！