PHP "array query"/多个查询

Question

我有一个 mysql 表，我想在其中执行查询。我的表格如下所示:

    date        activity    amount     --------    ------      --------     day 1       drink       0     day 1       eat         1     day 1       breath      1     day 2       drink       0     day 2       eat         0     day 2       breath      0     day 3       drink       1     day 3       breath      0     day 4       eat         1     etc              etc              etc          

我想要做的是查看何时 eat 为 1，对于事实如此的日子，我想显示那些日子的所有事件

//What I was doing right now is:$activityarray = array();$result = mysql_query("SELECT * FROM table WHERE activity='eat' AND amount='1'");$row = mysql_fetch_assoc($result);//this returns all rows where activity=eat and amount=1do{     //perform for each result row a new query; look for the 'date'=$row[date] from the first query and show all activities that have been done that day (activity=1)     $result2 = mysql_query("SELECT * FROM table WHERE date='".$row[date]."'");     $row2 = mysql_fetch_assoc($result2);     do{         array_push($activityarray,$row2['activity']);     }while($row2 = mysql_fetch_assoc($result2));}while($row = mysql_fetch_assoc($result));print_r($activityarray);

由于有数千天，每天有数十个事件，这对我来说似乎不是最有效的方法。有没有一种方法可以通过一个查询更有效地完成此操作？ (因此:检查进食天数的所有事件=1)。希望有人能帮我出去!

Answer 1

使用自连接:

SELECT t1.*
FROM table AS t1
JOIN table AS t2 ON t1.date = t2.date
WHERE t2.activity = 'eat' AND t2.amount = 1