mySQL嵌套SELECT问题

Question

我在嵌套 SELECT 时遇到问题我收到以下错误消息:“对于需要 '(' 的此服务器版本，选择在此位置无效”

SELECT group_id, count (student_id) 
FROM
(
SELECT student.group_id, student.student_id, AVG(mark)
    FROM
    student, group, result
    WHERE
        student.student_id = result.student_id 
        and
        student.group_id = group.group_id
        GROUP BY student.group_id, student.student_id
) AS ”p”
p.AVG > 4
GROUP BY group_id
ORDER BY count (student_id) DESC
LIMIT 1;

我不明白如何解决它。请帮助我!

提前感谢您的回答

Answer 1

我将从修复两件事开始:

• group 是保留字。如果您将其用作表名(尽量不要)，则需要引用它。
• 将表达式 AVG(mark) 命名为 avg_mark。
• 添加 WHERE 子句。

尝试以下查询:

SELECT group_id, count (student_id) 
FROM
(
SELECT student.group_id, student.student_id, AVG(mark) as avg_mark
    FROM
    student, `group`, result
    WHERE
        student.student_id = result.student_id 
        and
        student.group_id = `group`.group_id
        GROUP BY student.group_id, student.student_id
) p
WHERE avg_mark > 4
GROUP BY group_id
ORDER BY count (student_id) DESC
LIMIT 1;