我们有一个日志表,用户每次运行时都在其中处理日志条目(success
/ failure
/ timeout
)。例如
+----+----------+---------+
| id | username | status |
+----+----------+---------+
| 1 | saqib | success |
| 2 | mary | timeout |
| 3 | scott | timeout |
| 4 | saqib | success |
| 5 | mary | timeout |
| 6 | scott | timeout |
| 7 | saqib | timeout |
| 8 | mary | timeout |
| 9 | scott | timeout |
+----+----------+---------+
我们希望获得一个过去曾成功的用户名,但是最新的用户名是超时。 (在上面的示例中为saqib)
是否有单个查询可以做到这一点?现在,我们正在使用PHP脚本执行此操作,但希望对此使用mySQL查询。
谢谢
最佳答案
SELECT DISTINCT m1.username
FROM
(
SELECT s1.username, s1.ids
FROM
(
SELECT username, MAX(id) as ids
FROM MyTable
GROUP BY username
) AS s1
INNER JOIN MyTable AS s2
ON s1.ids = s2.id
WHERE s2.status = 'timeout'
) AS m1
INNER JOIN MyTable m2 ON m1.username = m2.username
AND m2.status = 'success'
关于mysql - SQL查询以比较表中的较早值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23918968/