根据其他行的内容从其他行获取数据

Question

我有一个数据表，可能如下所示。答案总是以6个一组的形式提交给用户。例如，如果有人对questionID 1回答了"1“，那么我需要知道他们对question4的回答是什么，或者如果他们对问题2回答了1，那么我需要知道他们对问题5的回答，如果他们对问题3的回答是1，那么我需要知道他们对问题6的回答。

user   questionID    response
1      1             2
1      2             3
1      3             1
1      4             5
1      5             5
1      6             2
2      1             1
2      2             6
2      3             3
2      4             3
2      5             2
2      6             5

在这件事上我真的需要一些帮助。非常感谢

Answer 1

这是你需要的吗？尝试运行此查询。

select u1.*, u2.response from users u1
left join users u2
on u1.user = u2.user and u1.questionID = u2.questionID - 3
where u1.response = 1

子字符串(4中的‘inp1’)表示1:

select u1.*, u2.response from users u1
left join users u2
on u1.user = u2.user and
  substring(u1.questionID FROM 4) = substring(u2.questionID FROM 4) - 3
where u1.response = 1

Answer 2

我认为你想要一个“轴心”，在那里你可以得到所有的答案：

select
  t1.response as response1,
  t2.response as response2,
  t3.response as response3,
  t4.response as response4,
  t5.response as response5,
  t6.response as response6
from mytable t1
left join mytable t2 on t1.user = t2.user and t2.questionId = 2
left join mytable t3 on t1.user = t3.user and t3.questionId = 3
left join mytable t4 on t1.user = t4.user and t4.questionId = 4
left join mytable t5 on t1.user = t5.user and t5.questionId = 5
left join mytable t6 on t1.user = t6.user and t6.questionId = 6
where t1.user = ?

我会将其设为视图，并将t1.user添加为列，然后您只需从视图where user =？中选择。