我们有一个日志表,用户每次运行日志条目时都会处理日志条目(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查询。
谢谢
发布于 2014-05-28 18:28:47
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'发布于 2014-05-28 18:17:21
您可以检索每个username的最新username,然后使用原始表检查是否存在status、success和id小于最大值的每个用户的条目。
SELECT t.*
FROM ( SELECT username
, MAX(id) as ind
FROM tbl
GROUP BY username
) x JOIN tbl t ON t.username = x.username
AND t.id = x.ind
AND t.status IN ('timeout', 'failure')
AND EXISTS ( SELECT *
FROM tbl
WHERE username = x.username
AND id < x.ind
AND status = 'success'
)发布于 2014-05-28 18:30:12
我会用存在来解决这个问题。存在是很好的,因为它们通常比加入表更快。不相关,但我建议使用时间戳,而不是依赖id号。
Select username
From table t1
Where t1.status = 'timeout'
and exists (Select 1
From table t2
Where t1.username = t2.username
and t2.status = 'success'
Limit 1)
and not exists (Select 1
From table t3
Where t3.username = t1.username
and t3.id > t1.id
Limit 1);https://stackoverflow.com/questions/23918968
复制相似问题