现有用户登录记录明细表user_login,记录部分字段如下:
username login_time
A 2012-9-1
A 2012-9-3
A 2012-9-5
B 2012-9-3
A 2012-9-7
C 2012-9-6
D 2012-9-8
要求用sql执行过后的结果是:
username login_time
B 2012-9-3
A 2012-9-7
C 2012-9-6
D 2012-9-8
我写的sql:
select username,login_time from user_login
group by username
结果是:
username login_time
A 2012-9-1
B 2012-9-3
C 2012-9-6
D 2012-9-8
改sql:
select username,login_time from user_login
group by username order by login_time desc
结果是:
username login_time
D 2012-9-8
C 2012-9-6
B 2012-9-3
A 2012-9-1
都不是我想要的结果,该如何实现呢?
username login_time
A 2012-9-1
A 2012-9-3
A 2012-9-5
B 2012-9-3
A 2012-9-7
C 2012-9-6
D 2012-9-8
要求用sql执行过后的结果是:
username login_time
B 2012-9-3
A 2012-9-7
C 2012-9-6
D 2012-9-8
我写的sql:
select username,login_time from user_login
group by username
结果是:
username login_time
A 2012-9-1
B 2012-9-3
C 2012-9-6
D 2012-9-8
改sql:
select username,login_time from user_login
group by username order by login_time desc
结果是:
username login_time
D 2012-9-8
C 2012-9-6
B 2012-9-3
A 2012-9-1
都不是我想要的结果,该如何实现呢?
FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE a.username=b.username AND a.login_time<b.login_time)
group by username
from user_login
group by username
[征集]分组取最大N条记录方法征集,及散分....
+----------+---------------------+
| username | login_time |
+----------+---------------------+
| A | 2012-09-01 00:00:00 |
| A | 2012-09-03 00:00:00 |
| A | 2012-09-05 00:00:00 |
| B | 2012-09-03 00:00:00 |
| A | 2012-09-07 00:00:00 |
| C | 2012-09-06 00:00:00 |
| D | 2012-09-08 00:00:00 |
+----------+---------------------+
7 rows in set (0.00 sec)mysql> SELECT * FROM TT6 A WHERE NOT EXISTS(SELECT 1 FROM TT6 WHERE A.`username`
=`username` AND A.`login_time`<`login_time`);
+----------+---------------------+
| username | login_time |
+----------+---------------------+
| B | 2012-09-03 00:00:00 |
| A | 2012-09-07 00:00:00 |
| C | 2012-09-06 00:00:00 |
| D | 2012-09-08 00:00:00 |
+----------+---------------------+
4 rows in set (0.00 sec)mysql>
在ORACLE中是一样的