mysql - php mysql匹配及返回值

Question

我的表是:

studens_exam_answers_tbl
----------------------------------------------------
exam_id | question_id | student_id  | student_answer 
----------------------------------------------------
    10       1           6              1 
    10       2           6              4
    10       3           6              3
    10       4           6              0
    10       5           6              5
    10       1           5              1
    10       2           5              2
    10       3           5              2
    10       4           5              5
    10       5           5              5

question_poll__tbl
--------------------------------------------------------------------------------
question_id | question  | option_1 |  option_2 | ....| option_5 |correct_answer
--------------------------------------------------------------------------------
    1          ....         ....       ....      ...    ....        5
    2          ....         ....       ....      ...    ....        2
    3          ....         ....       ....      ...    ....        3
    4          ....         ....       ....      ...    ....        4
    5          ....         ....       ....      ...    ....        1

我想用sql匹配学生答案和正确答案。如果学生回答正确，则“这是真的”，否则为“假”。如果 student_answer 为 0 '空'。

我该怎么做？

Answer 1

试试这个

 Select student id, (Case when student_answer=0
    then ""
    else
    when student_answer=correct_answer then "true"
    else "false"
    end ) as Result
    from studens_exam_answers_tbl inner join question_poll__tbl on studens_exam_answers_tbl.question_id.question_id =question_poll__tbl.question_id