mysql - SQL查询获取用户不同意

Question

我有一些针对不同情况的分析数据。每个案例都与一张或多张照片相关联。每张照片由两名用户分析。存储的数据看起来像

我想要的是让 SQL 查询生成如下所示的不一致结果

要么

或

有人可以帮助我实现这一目标吗？

感谢您分享您的宝贵时间。

这是要测试的示例数据

Case Id,Photo Id,FeatureCheck,Result,CheckedBy 
17116,173442,severity,none,24 
17116,173442,severity,low,25 
17116,175062,severity,none,26 
17116,175062,severity,none,27 
17116,175427,severity,medium,24 
17116,175427,severity,high,28 
17116,175748,severity,low,22 
17116,175748,severity,none,30 
17116,176031,severity,low,24 
17116,176031,severity,low,29 
17277,175309,severity,none,24 
17277,175309,severity,none,25 
17277,175649,severity,none,24 
17277,175649,severity,none,25

Answer 1

您可以使用INNER JOIN

SELECT
 t1.`Case Id`,  t1.`Photo Id`,t1.FeatureCheck,t1.CheckedBy,t1.Result,t2.CheckedBy,t2.Result
FROM test t1
 JOIN test t2
ON (t1.`Case Id`=t2.`Case Id` 
    AND  t1.`Photo Id`= t2.`Photo Id` 
    AND t1.CheckedBy!=t2.CheckedBy 
    AND t1.CheckedBy<t2.CheckedBy)
WHERE t1.Result!=t2.Result;

DEMO