Sql查询空值插入

Question

Sql查询

SELECT      respondant.respondant_firstname     as first_name,
            question.question_id                as question_id,                                        
            answer.answer_id,
            answer.answer_text,
            answer.answer_rate,
            answer.answer_nps,
            question_radio.question_radio_text  as opt 
FROM        question 
LEFT JOIN   answer          on  answer.answer_question_id = question.question_id 
LEFT JOIN   question_radio  on  answer.answer_question_radio_id = question_radio.question_radio_id
LEFT JOIN   respondant      on  answer.answer_respondant_id = respondant.respondant_id 
WHERE       question.question_feedback_id = 1                                         
ORDER BY    question.question_id, answer.answer_id

输出：

first_name question_id answer_id answer_text answer_rate answer_nps  opt
RM            1          1                     5         NULL       NULL
Y             1          3                     0         NULL       NULL
Ben           1          5                     0         NULL       NULL
akash         1          8                    2.5        NULL       NULL
RM            2          2                     0           4        NULL
Y             2          4                     0           3        NULL
Ben           2          6                     0           0        NULL
akash         2          9                     0           0        NULL
Ben           3          7         Thanks      0           0        NULL
akash         3          10                    0           0        NULL

我需要输出为：

first_name question_id answer_id answer_text answer_rate answer_nps  opt
RM            1          1                     5         NULL       NULL
Y             1          3                     0         NULL       NULL
Ben           1          5                     0         NULL       NULL
akash         1          8                    2.5        NULL       NULL
RM            2          2                     0           4        NULL
Y             2          4                     0           3        NULL
Ben           2          6                     0           0        NULL
akash         2          9                     0           0        NULL
RM            3          NULL                  0           0        NULL
Y             3          NULL                  0           0        NULL
Ben           3          NULL        ThankS    0           0        NULL
akash         3          NULL                  0           0        NULL

从最后一行起的第三行和第四行中没有id 3，但我需要替换为3，这两行中的其他值必须为空

Answer 1

您想要做的事情相当复杂。你想为每个回答者和问题排一行，然后填写回答者的详细信息。我的猜测是，您希望在可用的情况下填写answer_id。也就是说，您想要的结果应该具有"Ben"one_answers"Akash"的answer_ids。

以下查询使用响应者和问题之间的cross join生成所有行。然后它带来了额外的信息：

SELECT r.respondant_firstname as first_name, q.question_id as question_id,                                        
       a.answer_id, a.answer_text, a.answer_rate, a.answer_nps,
       qr.question_radio_text  as opt 
FROM respondant r CROSS JOIN
     question q LEFT JOIN
     answer a
     on a.answer_question_id = q.question_id and
        a.answer_respondant_id = r.respondant_id LEFT JOIN
     question_radio  qr
     on a.answer_question_radio_id = qr.question_radio_id              
WHERE q.question_feedback_id = 1                                         
ORDER BY q.question_id, a.answer_id;