php - 从数据库中选择项目与另一个表中的最新日期进行比较

Question

我想从 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 函数。所有值都会相应地转义和清理。

Answer 1

我认为这符合你的要求:

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
                 ) ;