MYSQL在同一表中选择 [英] MYSQL Select within same table

问题描述

我有一个需要MYSQL Select语句的表，以便我可以找到损坏的数据并手动进行修复.

I have a table that I need a MYSQL Select statement so I can find the corrupt data and manually fix it.

样品表

TABLE_A
id | type | value1 | value2
 1 |  10  | 123    | 987  
 2 |  10  | 123    | 987
 3 |  10  | 123    | 789
 4 |  20  | 123    | 987
 5 |  20  | 456    | 987
 6 |  30  | 123    | null
 7 |  30  | 123    | null
 8 |  40  | 123    | 987

我需要一条select语句，该语句将列出记录，如果它们具有相同的类型"且value1不相同和/或value2不相同.

I need a select statement that will list records that if they have the same "type" and value1 is not the same and/or value2 not the same.

例如

• ID 1,2,3-将显示，因为value2在id 3中不同，并且具有相同的类型"

• ID 1,2,3 - will be displayed because value2 is different in id 3 and they have the same "type"

将显示ID 4,5，因为value1不同并且它们具有相同的类型"

ID 4,5 will be displayed because value1 is different and they have the same "type"

ID 6,7将不会显示，因为相同的类型"的value1和value2相同

ID 6,7 will NOT be displayed because value1 and value2 are the same for the same "type"

ID 8将不会显示，因为只有一种与此类型.

ID 8 will NOT be displayed because there is only one with this type.

几天来，我一直在努力寻求帮助，需要一些帮助.谢谢

I have been trying to get my head around this for days and need some help. Thanks

推荐答案

SELECT a.id as problem_id
FROM TABLE_A as a JOIN TABLE_A as b
ON a.type = b.type
WHERE a.value1 <> b.value1 OR a.value2 <> b.value2
GROUP BY problem_id;

这篇关于MYSQL在同一表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！