sql - 为什么 AVG() 没有给出结果？

Question

我尝试只选择体重高于所有狗的平均体重的狗。

我有这个 SQL 查询:

SELECT nameAni, weigth
   FROM Animal
WHERE idTpAni IN (SELECT idTpAni
            FROM TypeAnimal
          WHERE libTpAni = 'DOG')
GROUP BY nameAni
HAVING AVG(weigth) < weigth;

如果我给小费 30 而不是 AVG(weigth)，我会得到我想要的结果。

你能解释一下为什么我在输入“AVG(...)”时没有结果吗？

Answer 1

SELECT nameAni, weigth
FROM Animal
WHERE idTpAni IN (SELECT idTpAni
                  FROM   TypeAnimal
                  WHERE  Lower(libTpAni) = 'dog')
                  AND    weigth > (SELECT Avg(weigth)
                                   FROM   Animal
                                   WHERE  idTpAni IN (SELECT idTpAni
                                   FROM   TypeAnimal
                                   WHERE  Lower(libTpAni) = 'dog'))

如果想走的简单一点，引用下面

    DECLARE @weight FLOAT; --mind the type of this variable

    SELECT @weight=Avg(weigth)
    FROM   Animal
    WHERE  idTpAni IN (SELECT idTpAni
    FROM   TypeAnimal
    WHERE  Lower(libTpAni) = 'dog')

    SELECT nameAni, weigth
    FROM   Animal
    WHERE  idTpAni IN (SELECT idTpAni
    FROM   TypeAnimal
    WHERE  Lower(libTpAni) = 'dog')
    AND    weigth > @weight