MySQL选择检测组内互斥的行冲突(MySQL select to detect mutually exclusive row conflicts within groups)

MySQL选择检测组内互斥的行冲突(MySQL select to detect mutually exclusive row conflicts within groups)

使用以下示例数据，我想创建一个查询，该查询可以找到item / desc重复的位置，并且它们各自的数据字段不是互斥的。 含义...对于给定的item / desc组合，列中只能有1个数据值。

样本表记录：

id | item | desc | data1 | data2 | data3 ----+-------+-------+-------+-------+------- 1 | 1 | cat | a | | 2 | 1 | cat | | b | 3 | 1 | cat | | e | 4 | 2 | dog | a | | 5 | 2 | dog | | h | f 6 | 3 | apple | k | | m 7 | 3 | worm | a | g | x 8 | 4 | rock | p | | s 9 | 4 | rock | | | s 10 | 4 | rock | | t | z

预期的查询结果：

item | desc -------+------- 1 | cat (because of conflict in data2 with b & e) 4 | rock (because of conflict in data3 with s,s & z

With the following sample data, I want to create a query that can find where item/desc is duplicate and their respective data fields are not mutually exclusive. Meaning... there can be only 1 data value in column for a given combination of item/desc.

Sample table records:

id | item | desc | data1 | data2 | data3 ----+-------+-------+-------+-------+------- 1 | 1 | cat | a | | 2 | 1 | cat | | b | 3 | 1 | cat | | e | 4 | 2 | dog | a | | 5 | 2 | dog | | h | f 6 | 3 | apple | k | | m 7 | 3 | worm | a | g | x 8 | 4 | rock | p | | s 9 | 4 | rock | | | s 10 | 4 | rock | | t | z

Expected query result:

item | desc -------+------- 1 | cat (because of conflict in data2 with b & e) 4 | rock (because of conflict in data3 with s,s & z

最满意答案

这应该工作：

select distinct item, `desc` from table group by item, `desc` HAVING count(distinct data1) > 1 or count(distinct data2) > 1 or count(distinct data3) > 1

This should work:

select distinct item, `desc` from table group by item, `desc` HAVING count(distinct data1) > 1 or count(distinct data2) > 1 or count(distinct data3) > 1