MySQL和列并将其与给定值进行比较

Question

我需要显示DB的一组可用空间的结果，其中的条件是：

1. alm_num =1
2. 2012年11月26日至29日期间的alm_date
3. 和sum_num >或=3

然后我写成这样的mySQL：

select sum(villas_allotment.alm_num) as sum_num, 
villas_allotment.alm_id as aid, villas_db.v_name as vname  
from villas_allotment 
inner join villas_db on villas_allotment.alm_vid=villas_db.v_id 
where alm_num='1' 
and alm_date between '2012-11-26' and '2012-11-29' 
and alm_vid='3' 
and sum_num>='3'

但我有：

#1054 - Unknown column 'sum_num' in 'where clause'

所以你们能不能建议我如何从这个错误中生存下来。致以敬意，

Answer 1

对于聚合列的条件，需要使用HAVING：

select
    sum(villas_allotment.alm_num) as sum_num, 
    villas_allotment.alm_id as aid,
    villas_db.v_name as vname  
from villas_allotment 
inner join villas_db on villas_allotment.alm_vid = villas_db.v_id 
where alm_num = '1' 
and alm_date between '2012-11-26' and '2012-11-29' 
and alm_vid = '3' 
group by villas_allotment.alm_id, villas_db.v_name
having sum(villas_allotment.alm_num) >= 3

您还需要在非聚合列上添加一个group by子句，这是我为您添加的。