php - 如何检测mySQL数据库的分组结果是否有相关值？

Question

animal    food
cat
cat       mouse
cat       
dog
dog
bird      carrot
bird      herb
bird

我的 mysql 数据库中有一些动物。我的问题是我需要知道动物群中的任何动物是否有食物。

 $pdo = $db->query('SELECT * FROM list GROUP BY animal');
 while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
    if($row["food"]){
       echo "Some ".$row["animal"]." got food.<br>";
    } else {
       echo "No " .$row["animal"]. " got food.<br>";
  }
} 

我的结果是:

No cat got food.
No dog got food.
No bird got food.

但我希望:

Some cat got food.
No dog got food.
Some bird got food.

Answer 1

我们可以使用聚合函数，例如

 SELECT t.animal
      , MAX(t.food) AS food
   FROM list t 
  GROUP BY t.animal
  ORDER BY t.animal

或条件聚合，例如:

 SELECT t.animal
      , COUNT(IF(t.food>'',1,NULL)) AS food
   FROM list t
  GROUP BY t.animal
  ORDER BY t.animal