是否可以在别处使用别名?
问题描述:
是否可以使用来自WHERE
子句的别名from avg(n.nota) as media
?是否可以在别处使用别名?
SELECT a.nome AS nome, c.nome AS curso, avg(n.nota) AS media from Aluno a
JOIN Matricula m ON m.aluno_id = a.id
JOIN Curso c ON m.curso_id = c.id
JOIN Secao s ON s.curso_id = c.id
JOIN Exercicio e ON e.secao_id = s.id
JOIN Resposta r ON r.exercicio_id = e.id and r.aluno_id = a.id
JOIN Nota n ON n.resposta_id = r.id
WHERE media > 6 GROUP BY nome;
错误:ERROR 1054 (42S22): Unknown column 'media' in 'where clause'
我使用加盟列组合在一起,我不希望使用子查询,像this question
提到是否有可能呢?
答
为了使用上集合函数的滤波器,利用HAVING
子句代替WHERE
子句:
SELECT a.nome AS nome, c.nome AS curso, avg(n.nota) AS media from Aluno a
JOIN Matricula m ON m.aluno_id = a.id
JOIN Curso c ON m.curso_id = c.id
JOIN Secao s ON s.curso_id = c.id
JOIN Exercicio e ON e.secao_id = s.id
JOIN Resposta r ON r.exercicio_id = e.id and r.aluno_id = a.id
JOIN Nota n ON n.resposta_id = r.id
GROUP BY nome
HAVING avg(n.nota) > 6;