基于有效SQL创建的视图打开时报#1242子查询返回多行错误

执行以下SQL语句可得到预期结果：

SELECT Users.idQuiz as ThisIDQuiz,Rounds.RoundNr as ThisRoundNr, Questions.QuestionNr as ThisQuestionNr, Answer as ThisAnswer, Questions.QuestionScore AS QuestionScoreMax,AnswerCorrect,
(SELECT COUNT(*) as "Aantal Ploegen met dit antwoord"
FROM `Answers`
JOIN Questions on Answers.idQuestion = Questions.idQuestion
JOIN Rounds on Questions.idRound = Rounds.idRound
JOIN Users on Users.idUser = Answers.idUser
 where (Users.idQuiz = ThisIDQuiz AND Rounds.RoundNr = ThisRoundNr AND Questions.QuestionNr=ThisQuestionNr AND Answers.Answer = ThisAnswer )
GROUP BY Users.idQuiz,Rounds.RoundNr, Questions.QuestionNr,Answer
) as NrOfTeamsWithThisAnswer,
(SELECT COUNT(*)
 FROM Users
 WHERE ((Users.idQuiz = ThisIDQuiz) AND (Users.UserType = 0))
 ) As TotalNrOfTeams,
 AnswerCorrect *((Select TotalNrOfTeams)- (SELECT NrOfTeamsWithThisAnswer))as ScoreForThisAnswer
FROM `Answers`
JOIN Questions on Answers.idQuestion = Questions.idQuestion
JOIN Rounds on Questions.idRound = Rounds.idRound
JOIN Users on Users.idUser = Answers.idUser
WHERE Questions.QuestionType = 5
GROUP BY ThisAnswer
ORDER BY ThisIDQuiz, ThisRoundNr, ThisQuestionNr, ThisAnswer;

使用该语句创建视图成功，但打开视图时出现错误#1242 - Subquery returns more than 1 row，删除重建视图后问题依旧；移除倒数第二行的GROUP BY ThisAnswer后，创建的视图可正常打开使用。使用工具为phpMyAdmin 5.1.3。

报错原因 #

1. GROUP BY子句逻辑不严谨
   原SQL外层仅按ThisAnswer分组，但SELECT列表中包含ThisIDQuiz、ThisRoundNr、ThisQuestionNr等多个未纳入分组的列。同一个ThisAnswer可能对应多组不同的测验、轮次、题目组合，导致分组后这些列的值无法唯一确定。

2. 子查询返回多行触发限制
   子查询NrOfTeamsWithThisAnswer依赖外层的ThisIDQuiz、ThisRoundNr、ThisQuestionNr、ThisAnswer四个字段过滤分组。当外层仅按ThisAnswer分组后，前三个字段的值不再唯一，子查询会匹配到多条符合条件的记录，最终返回多行结果——而MySQL不允许SELECT列表中的标量子查询返回多行，因此触发#1242错误。

3. 移除GROUP BY后正常的逻辑
   去掉GROUP BY ThisAnswer后，SQL返回每一条独立的Answer记录，此时ThisIDQuiz、ThisRoundNr、ThisQuestionNr、ThisAnswer都是当前行的唯一值，子查询只会匹配到唯一的分组结果，返回单行数据，不会触发多行返回的错误。

内容的提问来源于stack exchange，提问作者RomeoMike