Mysql查询count出错

Question

以下 mysql 查询...

SELECT a.*, b.*,
       ( 
            SELECT COUNT( * ) 
            FROM lp_units c 
            WHERE c.property_id = a.property_id 
        ) AS unitcount
FROM lp_property a,
     lp_property_confidential b
WHERE a.property_id = b.property_id
AND c.unitcount<= a.no_of_units
AND a.account_id = '1'

返回错误...

Unknown column 'c.unitcount' in 'where clause'

我认为我的疑问是可以理解的。解决它即可运行....

提前致谢...

Answer 1

不要使用c.unitcount。只是unitcount。 unitcount 不是 c 的列，而是子查询生成的临时表的列。

但是，无论如何，将此查询编写为联接可能会更好。

尝试这个查询

SELECT
  a.*,
  b.*,
  COUNT(c.property_id) as unitcount
  FROM lp_property a
  JOIN lp_property_confidential b ON a.property_id = b.property_id
  JOIN lp_units c ON c.property_id = a.property_id
  WHERE
  a.account_id = '1'
  GROUP BY a.property_id
  HAVING unitcount <= a.no_of_units