+ -------- + ------ + --------------------- + | userId | Cmd | 时间| + -------- + ------ + --------------------- + | 1 | A | 2013-06-21 13:15:08 | | 1 | B | 2013-07-31 15:58:53 | | 3 | A | 2013-07-31 16:01:42 | | 4 | A | 2013-05-01 11:05:08 | | 4 | B | 2013-07-31 16:06:32 | | 6 | B | 2013-07-31 17:16:08 | | 7 | A | 2013-04-06 17:20:36 | | 3 | B | 2013-04-15 06:26:14 | | 7 | B | 2013-07-31 14:53:49 | | 6 | A | 2013-07-31 09:56:27 | + -------- + ------ + --------------------- +
如何获取最后一个Cmd为B的用户ID？
预期结果：
+ -------- + ------ + --------------------- + | userId | Cmd | 时间| + -------- + ------ + --------------------- + | 1 | B | 2013-07-31 15:58:53 | | 4 | B | 2013-07-31 16:06:32 | | 6 | B | 2013-07-31 17:16:08 | | 7 | B | 2013-07-31 14:53:49 | + -------- + ------ + --------------------- +
在这里，你需要自联接使用MAX(Time)，UserId而Cmd='B'像这样：
SELECT t1.* FROM Table1 t1
JOIN
(
SELECT UserId, MAX(Time) MaxTime FROM Table1
GROUP BY UserID
)t2
ON t1.UserID = t2.UserId
AND t1.Time = t2.MaxTime
AND t1.Cmd = 'B';
输出：
| USERID | CMD | TIME |
----------------------------------------------
| 1 | B | July, 31 2013 15:58:53+0000 |
| 4 | B | July, 31 2013 16:06:32+0000 |
| 6 | B | July, 31 2013 17:16:08+0000 |
| 7 | B | July, 31 2013 14:53:49+0000 |
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句