从数据库中选择与另一个表中最新日期比较的项 - SELECT items from DB comparing to latest date in another table

SELECT items from DB comparing to latest date in another table

我想从userLogTable中选择记录比activityTable中最新匹配项更新的所有项目。我想在一个查询中做到这一点。

目前我从一个表中进行选择，然后在与另一个表匹配时循环遍历结果。

SELECT COUNT(*) AS Visits, userLogTable.* FROM userLogTable GROUP BY Name, Date
userLogTable
Name   | Surname | Date
-----------------------------
Dave   | Smith   | 2016-06-01
Jane   | Doe     | 2016-06-01
Dave   | Smith   | 2016-06-02
Dave   | Smith   | 2016-06-01
Jane   | Doe     | 2016-06-03
Peter  | Bloggs  | 2016-06-03
Steve  | Foo     | 2016-06-01
Steve  | Foo     | 2016-06-01
// many more rows
// above SQL returns the following result as expected/needed
Name   | Surname |  Date       | Visits
----------------------------------------
Dave   | Smith   |  2016-06-01 | 2
Jane   | Doe     |  2016-06-01 | 1
Dave   | Smith   |  2016-06-02 | 1
Jane   | Doe     |  2016-06-03 | 1
Peter  | Bloggs  |  2016-06-03 | 1
Steve  | Foo     |  2016-06-01 | 2
activityTable
Name   | Surname | Date
------------------------------
Dave   | Smith   | 2016-06-03
Dave   | Smith   | 2016-06-03
Dave   | Smith   | 2016-06-03
Dave   | Smith   | 2016-06-02
Dave   | Smith   | 2016-06-02
Dave   | Smith   | 2016-06-02
Dave   | Smith   | 2016-06-01
Dave   | Smith   | 2016-05-29
Dave   | Smith   | 2016-05-29
// many more rows

查询:

foreach($userLogTableResult as $key => $val) {
    // db function
    SELECT Date 
    FROM activityTable  
    WHERE Date > $latestDateFromUserLogTable 
      AND NAME = $val['Name'] 
      AND Surname = $val['Surname'] 
    ORDER BY Date DESC LIMIT 1 
    // if there is a result then unset this item as it's older than the latest activity
}

任何帮助都将是非常感激的。

上面的代码在这个例子中被大大简化了。我正在构建SQL并将其解析为自定义PDO函数。

我认为这是你想要的:

select ult.*
from userLogTable ult
where ult.date > (select max(a.date)
                  from activityTable a
                  where a.name = ult.name and a.surname = ult.surname
                 ) ;