mysql - 在mysql查询中输出重复

我有4表数据是：

Question_Paper_Master：此表包含用于测试（考试）的那些问题的问题ID。

Question_Paper_Id   Question_Id   Test_Id
1                     1              1
2                     2              1
3                     3              1
4                     4              1

Question_Bank：此表包含问题列表。

Question__Id      Question            Question_Type_id
1                     abc               1
2                     pqr               1
3                     lmn               1
4                     xyz               1

Question_Type：此表包含的问题数据类型为单选（单选按钮）或多选（复选框）。

Question__type_id        Question_type
1                        single choice
2                        multiple choice

Option_Master：此表包含问题选项列表。

option__Id        Question_id            option
1                     1                    a
2                     1                    b
3                     1                    c
4                     1                    d
.
.
.

现在的问题是我想通过问题来获取选项及其类型（单个或多个）并在datatable中获取输出。

我的查询是：

select q.Question,o.Options,t.Question_Type
    from Question_Paper_Master Qp,Question_Bank Q,Option_Master o,Question_Type_Master T
    where qp.Question_Id=q.Question_Id and q.Question_Id=o.Question_Id
    and q.Question_Type_Id=t.Question_Type_Id
    and qp.Test_Id=9

但是输出如下：

question      option      questiontype
 abc           a            singlechoice
 abc           b            single choice
 abc           c            singlechoice
 abc           d            single choice
 pqr           a            singlechoice
 pqr           b            single choice
 pqr           c            singlechoice
 pqr           d            single choice

我想要这样的输出：

question      option      questiontype
 abc           a           singlechoice
               b
               c
               d
pqr            a            singlechoice
               b
               c
               d

最佳答案

SELECT question, `option`, questiontype
FROM (
    SELECT IF(question = @prev_question, '', question) AS question,
           `option`,
           IF(question = @prev_question and question_type = @prev_type, '', question_type) AS questiontype,
           @prev_question := question, @prev_type := question_type
    FROM (select q.Question,o.`Option`,t.Question_Type
          from Question_Paper_Master Qp,Question_Bank Q,Option_Master o,Question_Type T
          where qp.Question_Id=q.Question_Id and q.Question_Id=o.Question_Id
          and q.Question_Type_Id=t.Question_Type_Id
          ORDER BY question, question_type) x
    CROSS JOIN (SELECT @prev_question := null, @prev_type := null) vars
) subq

DEMO

关于mysql - 在mysql查询中输出重复，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24881741/