如果大于0，则计算行数和返回结果

问题描述：

I have try to display result only if content > 0

SELECT actors.*, (SELECT count(*) FROM `content_actors`
                  WHERE content_actors.actor = actors.record_id) AS getCount
FROM actors

If i try to add in query

WHERE getCount > 0

I will have error

Unknown column 'getCount' in 'where clause'

我试图仅在内容＆gt;时显示结果 0 p>

  SELECT actors。*，（SELECT count（*）FROM`content_actors` 
 WHERE content_actors.actor = actors.record_id）AS getCount 
FROM actors 
   code>  pre> 
 
 如果我尝试添加查询 p> 
 
 
  WHERE getCount＆gt;  0 
  code>  pre> 
 
 我将有错误 p> 
 
 
 
 'where子句'中的未知列'getCount'  p> 
  blockquote> 
  div>

答

In MySQL, you can use a having clause:

SELECT actors.*,
       (SELECT count(*) FROM `content_actors` WHERE content_actors.actor = actors.record_id) AS getCount
FROM actors
HAVING getCount > 0;

This is a MySQL extension.

Assuming actors.record_id is unique (presumably a primary key), this could be written as:

SELECT a.*, COUNT(*) as getCount
FROM actors a JOIN
     content_actors ca
     ON ca.actor = a.record_id
GROUP BY a.record_id;  -- assumes this is unique/primary key

No filtering is needed, because the JOIN requires at least one match.

如果大于0，则计算行数和返回结果

相关推荐