mysql基础 依据一个字段查询另外一个字段存在不同的值

原数据 表score

需求：查询出name对应存在不同year的人

SELECT s1.* FROM `score` s1 where exists(SELECT 1 FROM score s2 where s2.id != s1.id AND s2.`name` = s1.`name` AND s2.`year` != s1.`year`);//(自身关联，id不同（确保不是自身），name不同（需求）,year相同)

或者SELECT DISTINCT s.* from score s,score s1 where s.id != s1.id and s.`name` = s1.`name` AND s.`year` != s1.`year`;