在SQL查询的WHERE子句中使用自定义字段

My question is very similar to this one How to reference a custom field in SQL I have the following query:

我的问题与此非常相似如何在SQL中引用自定义字段我有以下查询:

SELECT * , (SELECT COUNT( id ) 
FROM cms_store_items
WHERE speaker = cms_store_items_speakers.id
) AS count
FROM cms_store_items_speakers
LIMIT 0 , 30

I need to add a WHERE clause that looks like WHERE count > 0 but when I do I get the error Unknown column 'count' in 'where clause' is there anyway for me to reference the custom field in my where clause without duplicating logic?

我需要添加一个看起来像WHERE count> 0的WHERE子句但是当我这样做时我得到错误'where子句'中的未知列'count'反正我在那里引用我的where子句中的自定义字段而没有重复逻辑?

I could just place the logic for the where clause in my code but I don't want to send what may well be more then 1000 rows to the app if not needed, it just seems like a waste of resources.

我可以在我的代码中放置where子句的逻辑,但如果不需要,我不想向应用程序发送可能超过1000行的内容,这似乎是浪费资源。

3 个解决方案

#1

Well, to do this strictly the way you're doing it:

好吧,严格按照你的方式做到这一点:

select
*
from
(
    SELECT * , (SELECT COUNT( id ) 
    FROM cms_store_items
    WHERE speaker = cms_store_items_speakers.id
    ) AS count
    FROM cms_store_items_speakers
) a
where a.count > 0
LIMIT 0 , 30

It would probably be better to do the following, though. It makes good use of the having clause:

但是,做以下事情可能会更好。它充分利用了having子句:

select
    s.id,
    s.col1,
    count(i.speaker) as count
from
    cms_store_items_speakers s
    left join cms_store_items i on
        s.id = i.speaker
group by
    s.id,
    s.col1
having
    count(i.speaker) > 0
limit 0, 30

#2

You can using HAVING clause instead:

您可以使用HAVING子句代替:

...
) AS count
FROM cms_store_items_speakers
HAVING count > 0
LIMIT 0 , 30

HAVING is like WHERE but it is able to work on columns which are computed. Warning: HAVING works by pruning results after the rest of the query has been run - it is not a substitute for the WHERE clause.

HAVING就像WHERE,但它能够处理计算的列。警告:HAVING通过在运行查询的其余部分后修剪结果来工作 - 它不能替代WHERE子句。

#3

I'm not 100% sure about mysql, but something like this should be very possible:

我不是100%肯定mysql,但这样的事情应该是非常可能的:

(SELECT 
     *
FROM
(
   SELECT cms_store_items_speakers.*,
          (SELECT COUNT(id) FROM cms_store_items
           WHERE speaker = cms_store_items_speakers.id) AS 'count'
   FROM cms_store_items_speakers)
   LIMIT 0, 30
)
WHERE count > 0;

#1