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

Question

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'

Answer 1

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.