对于每个成员，您需要与其他成员匹配的分数数。我首先要说：

select s.memberid, s2.memberid,
      ( (s1.score1 = s2.score1) + (s1.score2 = s2.score2) + (s1.score3 = s2.score3) +
        (s1.score4 = s2.score4) + (s.score5 = s2.score5) + (s1.score6 = s2.score6)
      ) as scores_in_common
from sample s join
     sample s2
     on s.memberid <> s2.memberid;

对于你正在做的事情来说，这可能已经足够了。信息在结果集中，但格式不同。
对于您的特定格式：

select memberid,
       group_concat(case when scores_in_common = 6 then memberid2 end) as in_common_6,
       group_concat(case when scores_in_common = 5 then memberid2 end) as in_common_5,
       group_concat(case when scores_in_common = 4 then memberid2 end) as in_common_4,
       group_concat(case when scores_in_common = 3 then memberid2 end) as in_common_3,
       group_concat(case when scores_in_common = 2 then memberid2 end) as in_common_2,
       group_concat(case when scores_in_common = 1 then memberid2 end) as in_common_1
from (select s.memberid, s2.memberid as memberid2,
             ( (s1.score1 = s2.score1) + (s1.score2 = s2.score2) + (s1.score3 = s2.score3) +
               (s1.score4 = s2.score4) + (s.score5 = s2.score5) + (s1.score6 = s2.score6)
             ) as scores_in_common
      from sample s join
           sample s2
           on s.memberid <> s2.memberid
     ) ss
where scores_in_common >= 1
group by memberid;