SQL查询获取协议列表 [英] SQL query to get agreement list

问题描述

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

I have some analytical data for different cases. Each case is associated with one or more photos. Each photo is analyzed by two users. The stored data looks like

我想要的是让SQL查询生成协议结果，如下所示

What I want is to have SQL query to generate agreement result as shown below

因此，对于案例17116，用户ID为26和27的照片175062达成了协议.类似的情况是用户ID为24和29的照片ID为176031.

So, for case 17116 there is agreement on photo 175062 from user id 26 and 27. Similar case is with photo id 176031 from user id 24 and 29.

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

Can somebody help me out to achieve this.

感谢您分享宝贵的时间.

Thanks for sharing your valuable time.

这是要用于测试的示例数据

Here is sample data to test with

案例ID，照片ID，功能检查，结果，已检查 17116,173442，严重程度，无，24 17116,173442，严重程度，低25 17116,175062，严重程度，无26 17116,175062，严重程度，无27 17116,175427，严重程度，中等，24 17116,175427，严重性，高，28 17116,175748，严重程度，低22 17116,175748，严重程度，无，30 17116,176031，严重性，低，24 17116,176031，严重性，低，29 17277,175309，严重程度，无，24 17277,175309，严重程度，无，25 17277,175649，严重程度，无，24 17277,175649，严重程度，无，25

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

推荐答案

SELECT caseid, photo_id , feature_check, agreedupon, 
   group_concat(checkedby SEPARATOR ',')  as listusers
FROM table1
GROUP BY case_id,  photo_id

假设有两个以上的用户检查数据.然后将它们分组会更加动态.

Asssuming the possibility of more than 2 users checked the data. Then grouping them is more dynamic.

这篇关于SQL查询获取协议列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！