尝试在查询的where部分中使用count列 [英] Trying to use a count column in the where part of a query

问题描述

有2个表

学生

• stuID
• camID FK

校园

• camID PK
• camName

我正在尝试找到有4名以上学生的校园，其中包括camName，camID(学生人数)

I am trying to find the campuses with more than 4 students that include the camName, camID, (number of students)

这是我到目前为止所得到的

This is what I got so far

SELECT 
    students.camID, campus.camName, SUM(students.stuID) as [count] 
FROM 
    students 
JOIN 
    campus ON campus.camID = students.camID 
WHERE 
    [count] > 3 
GROUP BY 
    students.camID, campus.camName
ORDER BY 
    [count]

这一切使我得到的是一个错误的无效的域名" count".

All this gets me though is a error that 'Invalid comlumn name 'count'.

推荐答案

您不能在WHERE子句中使用列别名，因为WHERE子句的计算是在之前甚至被创建.您也不能在HAVING子句中使用别名.

You can't use a column alias in a WHERE clause, because the WHERE clause is evaluated before the alias is even created. You also can't use an alias in the HAVING clause.

SELECT students.camID, campus.camName, COUNT(students.stuID) as studentCount
FROM students
JOIN campus
    ON campus.camID = students.camID
GROUP BY students.camID, campus.camName
HAVING COUNT(students.stuID) > 3
ORDER BY studentCount

这篇关于尝试在查询的where部分中使用count列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！