mysql - 在 WHERE 子句中使用计数值

Question

我正在尝试使用 COUNT 的结果来限制结果。例如，我只想显示计数 (matching_img_count) 大于/等于 2 的行。

SELECT tel_number.number, COUNT(DISTINCT(i2.id_img)) as matching_img_count
    FROM img_reference i
        JOIN img_reference i2 ON i.id_img = i2.id_img
            AND i2.id_tel=13777
        JOIN tel_number ON i.id_tel = tel_number.id_tel
WHERE tel_number.id_tel != 13777
GROUP BY i.id_tel, i2.id_tel

我尝试了两件事:

HAVING matching_img_count >= 2

和

WHERE matching_img_count >= 2

不走运...任何建议将不胜感激。

Answer 1

试试这个:

SELECT tel_number.number, COUNT(DISTINCT(i2.id_img)) as matching_img_count
    FROM img_reference i
        JOIN img_reference i2
             ON i.id_img = i2.id_img
        JOIN tel_number
             ON i.id_tel = tel_number.id_tel
WHERE tel_number.id_tel != 13777
       AND i2.id_tel=13777
GROUP BY i.id_tel, i2.id_tel
HAVING COUNT(DISTINCT(i2.id_img)) >= 2