MySQL запрос, показывающий ошибку: # 1241 — операнд должен содержать 1 столбца
Mysql запрос,
SELECT qcat.name,
COUNT( CASE WHEN qas.state = "todo" THEN 1 END ) AS gtotal,
COUNT( CASE WHEN qas.state = "gradedright" THEN 1 END ) AS rightanswer,
COUNT( CASE WHEN qas.state = "gradedwrong" THEN 1 END ) AS wronganswer,
SUM(qas.fraction) AS grade,
quiza.id
FROM mdl_quiz_attempts quiza
JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id
JOIN mdl_question qstn ON ( qa.`questionid` = qstn.id )
JOIN mdl_question_categories qcat ON ( qstn.`category` = qcat.id )
WHERE quiza.id=1173 and FIND_IN_SET(qstn.id, (1,2,3,4,5,6)) GROUP BY quiza.id,qcat.name
показывает ошибку: # 1241 — Операнд должен содержать 1 столбец
Решение
SELECT qcat.name,
COUNT( CASE WHEN qas.state = "todo" THEN 1 END ) AS gtotal,
COUNT( CASE WHEN qas.state = "gradedright" THEN 1 END ) AS rightanswer,
COUNT( CASE WHEN qas.state = "gradedwrong" THEN 1 END ) AS wronganswer,
SUM(qas.fraction) AS grade,
quiza.id
FROM mdl_quiz_attempts quiza
JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
JOIN mdl_question_attempt_steps qas ON qas.questionattemptid = qa.id
JOIN mdl_question qstn ON ( qa.`questionid` = qstn.id )
JOIN mdl_question_categories qcat ON ( qstn.`category` = qcat.id )
WHERE quiza.id=1173 and FIND_IN_SET(qstn.id, "1,2,3,4,5,6") GROUP BY quiza.id,qcat.name
Другие решения
Это не верно:
FIND_IN_SET(qstn.id, (1,2,3,4,5,6))
Так должно быть:
qstn.id IN (1,2,3,4,5,6)
или же:
qstn.id BETWEEN 1 AND 6
FIND_IN_SET() используется, когда список значений находится в строке через запятую, IN используется с литеральным списком в запросе.