如何在后台用这样的sql查询检索答案和问题表?

Question

我正在使用Servlet JSP SQL编写测验应用程序

我有两个表在sql像这样:

Question(questionID, question_content, status, subjectID)
Choice(choiceID, choice_content, questionID, isTrue)

1道题有4个答案(1对3错)

这是我的查询:

SELECT *, question_content
FROM Choice 
INNER JOIN Choice.questionID = Question.questionID

那么结果将是这样的:

问题1 -选择1

问题1 -选择2

问题1 -选择3

问题1 -选择4

…

所以后端接收到的对象会是

Obj(问题1,Choice1)

Obj(问题1,Choice2)

…

但是我想要的是:

Obj(Question1, Choice1, Choice2, Choice3, Choice4)

是否有任何方法来实现这一点或任何查询适合我的需要?

Answer 1

按如下方式使用条件聚合:

Select question_content,
Max(case when rn= 1 then choice_content end) as choice1,
Max(case when rn= 2 then choice_content end) as choice2,
Max(case when rn= 3 then choice_content end) as choice3,
Max(case when rn= 4 then choice_content end) as choice4
From
(SELECT c.choice_content, q.question_content, q.questionid,
Row_number() over (partition by q.questionid order by c.choice_id) as rn
FROM Choice c
INNER JOIN question q on c.questionID = q.questionID) t
Group by questionid, question_content