AS - WHERE not working

Question

    SELECT *,(SELECT count(*) FROM resimler WHERE ilanId=ilanlar.Id) AS
 sayi FROM ilanlar WHERE sayi>0

错误:#1054 -未知列'sayi'在'where子句' -

Answer 1

这是正确的行为。列别名在定义它们的SELECT或WHERE子句中不被识别。

一个选项是使用MySQL扩展名，并将WHERE子句替换为:

HAVING sayi > 0

在您的情况下，虽然，我可能只使用JOIN和显式GROUP BY:

select i.id, count(*)
from ilanlar i join
     resimler r
     on r.ilanid = i.id
group by i.id;