所以我想做的是,但这不起作用:
Select count(staffno)as total_staff,avg(salary),branchno
From staff
Where total_staff > 1
Group by branchno
这里是另一个例子:
Select salary,salary*1.015 as proposed_salary
From staff
Where proposed_salary > 50000
不能在查询的where
子句中使用聚合表达式(sum()
、count()
…(。该子句在行由group by
子句分组在一起之前进行求值,因此聚合尚不可用。
在SQL中,这就是having
子句的用途:
select count(staffno) as total_staff, avg(salary), branchno
from staff
having count(staffno) > 1 -- MySQL also allows "total_staff > 1"
group by branchno
对于第二个查询:select
子句中定义的别名在where
子句中也不可用(原因与上述相同(。您可以重复以下表达式:
select salary, salary * 1.015 as proposed_salary
from staff
where salary * 1.015 > 50000
或者您可以使用派生表(cte或子查询(:
select *
from (
select salary, salary * 1.015 as proposed_salary
from staff
) t
where proposed_salary > 5000