在多列mysql中查找重复值

Question

我很想找到一个简单的查询，在一张桌子。

表格示例：

PK, FK, Animal1, Animal2
001, 100, Dog, Cat
002, 100, Dog, Bird
003, 100, Rat, Mouse
004, 100, Fish, Dog
005, 200, Dog, Mouse

我想找到其中"；狗；在Animal1和Animal2中都是重复的。因此，在这种情况下，它将返回001、002、004…FK 100只能选择一次Dog。

Answer 1

此代码识别非唯一(FK，Animal(组合：

select FK, Animal, count(a.PK) count from (
select PK, FK, Animal1 Animal from table1
union
select PK, FK, Animal2 from table1) a
group by FK, Animal

另一个获取具有非唯一(FK，Animal(组合的所有行：

select * from table1
where FK = (select FK from (
select FK, Animal, count(a.PK) count from (
select PK, FK, Animal1 Animal from table1 union
select PK, FK, Animal2 from table1) a
group by FK, Animal
having count(*) > 1 ) b )
and ( Animal1 = (select Animal from (
select FK, Animal, count(a.PK) count from (
select PK, FK, Animal1 Animal from table1 union
select PK, FK, Animal2 from table1) a
group by FK, Animal
having count(*) > 1 ) b )
or   Animal2 = (select Animal from (
select FK, Animal, count(a.PK) count from (
select PK, FK, Animal1 Animal from table1 union
select PK, FK, Animal2 from table1) a
group by FK, Animal
having count(*) > 1 ) b )
)

dbfiddle这里的中间步骤(MySQL 8.0(

Answer 2

您可以通过一个简单的self-UNION来实现这一点。

例如，如果有一行有Dog, Dog，并且您需要两列，则包括每个Animal列而不使用匹配的别名，但您需要使用OR:过滤每个列

SELECT * 
FROM (SELECT * FROM animals_table 
UNION
SELECT * FROM animals_table) AS animals
WHERE animals.Animal1 = 'Dog'
OR animals.Animal2 = 'Dog';

sqlfiddle

如果您只想要PK、FK以及单行中与"Dog"匹配的任何动物值，则可以使用：

SELECT * 
FROM (SELECT animals_tt.PK, animals_tt.FK, animals_tt.Animal1 AS all_animals FROM animals_tt 
UNION 
SELECT animals_tt.PK, animals_tt.FK, animals_tt.Animal2 AS all_animals FROM animals_tt) AS animals
WHERE animals.all_animals = 'Dog';

sqlfiddle

UNION子句在一个名为all_animals:的列中创建了一个包含两个动物列的表

PK, FK, all_animals
001, 100, Dog
002, 100, Dog
003, 100, Rat
004, 100, Fish
005, 200, Dog
-------------------- 
001, 100, Cat
002, 100, Bird
003, 100, Mouse
004, 100, Dog
005, 200, Mouse

从那里，我们使用单个WHERE语句来过滤到只包含"Dog"的行。