php - 比较两个mysql表并显示变化

Question

我有一个名为存档的表:

| staff_id |   longname  | username | projectNo |  title   | process | creation_time |
|    01    |   John Doe  |   John   |    1701   | project1 | ANM(ld) |  2017-01-16   |
|    02    | Aaron Jones |   Aaron  |    1701   | project1 | ANM(ch) |  2017-01-16   |
|    01    |   John Doe  |   John   |    1702   | project2 | CGI(ld) |  2017-01-16   |
|    01    |   John Doe  |   John   |    1701   | project1 | ANM(ld) |  2017-03-16   |
|    02    | Aaron Jones |   Aaron  |    1701   | project1 | BGD(ch) |  2017-03-16   |
|    01    |   John Doe  |   John   |    1702   | project2 | CGI(ld) |  2017-03-16   |  
|    03    | James Bond  |   James  |    1702   | project2 | BGD(ld) |  2017-03-16   |
|    03    | Peter Parker|   Peter  |    1702   | project2 | BAD(ld) |  2017-03-16   |

我想从表中获取具有最小日期的行:

SELECT staff_id, longname, username, title, process, creation_time FROM archived
WHERE creation_time IN (SELECT min(creation_time) FROM archived)

以及具有最新日期的行:

SELECT staff_id, longname, username, title, process, creation_time FROM archived
WHERE creation_time IN (SELECT max(creation_time) FROM archived)

然后我想比较收到的表并仅显示更改和出现的行。所以从当前表中我只想得到:

| staff_id |   longname  | username | projectNo |  title   | process | creation_time |
|    02    | Aaron Jones |   Aaron  |    1701   | project1 | BGD(ch) |  2017-03-16   |
|    03    | James Bond  |   James  |    1702   | project2 | BGD(ld) |  2017-03-16   |
|    03    | Peter Parker|   Peter  |    1702   | project2 | BAD(ld) |  2017-03-16   |

有什么办法可以在单个查询中做到这一点吗？如果不是的话如何在php中完成？

Answer 1

如果我理解正确，您需要进行右连接

SELECT  t2.*
FROM    (
            SELECT  staff_id, longname, username, title, process, creation_time
            FROM    archived
            WHERE   creation_time IN (SELECT min(creation_time) FROM archived)
        ) t1
RIGHT JOIN (
            SELECT  staff_id, longname, username, title, process, creation_time
            FROM    archived
            WHERE   creation_time IN (SELECT max(creation_time) FROM 
        ) t2
ON      t1.staff_id = t2.staff_id AND
        t1.projectNo = t2.projectNo AND
        t1.process = t2.process
WHERE   t1.staff_id is null

右连接将保留第二个表(具有最大值的那个)的所有结果，并在第一个表的列上为它们提供 null(如果它们不匹配)。这样，您就可以过滤任何 null 的第一个表列，以仅获取不匹配的行。