mysql - 使用存储过程从表中获取数据

我有一张桌子叫
CUSTOMERANSWER表-（customeranswerID（pk），customerID（fk），surveyID（fk），质询（fk），Answer，answereddate）

我正在尝试通过特定的SurveyID来获取数据，其中QuestionID应等于No。问题表中的问题。

CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_feedbackAnswers`(IN `inputsurveyID` INT(11))
BEGIN
DECLARE _questionID INT;
DECLARE _questioncount INT;
SET _questionID = 1;
SET _questioncount = (SELECT count(questionID) From QUESTIONOPTION Where surveyID = inputsurveyID);
WHILE (_questionID <=(_questioncount+1))
DO
SELECT surveyID, questionID, answer
FROM CUSTOMERANSWER
WHERE surveyID = inputsurveyID AND questionID = _questionID;
SET _questionID = _questionID + 1;
END WHILE;
END

我得到的输出是



我想要的是一张桌子。这里给定了它的SurveyID，questionId，每次回答。

请帮忙。

最佳答案

代替：

WHILE (_questionID <=(_questioncount+1))
DO
    SELECT surveyID, questionID, answer
    FROM CUSTOMERANSWER
    WHERE surveyID = inputsurveyID AND questionID = _questionID;
    SET _questionID = _questionID + 1;
END WHILE;

尝试这个：

 SELECT surveyID, questionID, answer
 FROM CUSTOMERANSWER
 WHERE surveyID = inputsurveyID
 ORDER BY questionID