现有表如下:
id time is_login
3 2012-07-03 11:20:20 1
3 2012-07-03 11:25:20 0
3 2012-07-03 12:30:20 1
3 2012-07-03 12:20:50 0
is_login值等于1表示登录,等于0表示登出,那么如何通过一条sql语句查询出登录时间和登出时间呢?我需要的是这样显示:
id login_ time logout_time
3 2012-07-03 11:20:20 2012-07-03 11:25:20
3 2012-07-03 12:20:20 2012-07-03 12:30:50
id time is_login
3 2012-07-03 11:20:20 1
3 2012-07-03 11:25:20 0
3 2012-07-03 12:30:20 1
3 2012-07-03 12:20:50 0
is_login值等于1表示登录,等于0表示登出,那么如何通过一条sql语句查询出登录时间和登出时间呢?我需要的是这样显示:
id login_ time logout_time
3 2012-07-03 11:20:20 2012-07-03 11:25:20
3 2012-07-03 12:20:20 2012-07-03 12:30:50
from tb A
where is_login =1
1 100010102 2012/3/7 16:40:42 1 192.168.1.7
2 100010101 2012/3/7 16:42:06 1 192.168.1.105
3 100010101 2012/3/7 16:42:44 0
4 100010101 2012/3/7 16:43:12 1 192.168.1.105
5 100010101 2012/3/7 16:44:18 0
6 100010104 2012/3/7 16:44:24 1 192.168.1.105
7 100010102 2012/3/7 16:53:05 0
8 500010102 2012/3/7 16:53:47 1 192.168.1.7
9 500010102 2012/3/7 16:54:29 0
10 100010102 2012/3/7 16:54:32 1 192.168.1.7
11 100010102 2012/3/7 16:55:08 0
12 400010101 2012/3/7 16:55:40 1 192.168.1.7
13 400010101 2012/3/7 16:55:49 0
14 100010103 2012/3/7 16:55:51 1 192.168.1.7
15 100010103 2012/3/7 16:56:20 0
16 300010102 2012/3/7 17:05:08 1 192.168.1.36
17 100010102 2012/3/7 17:15:48 1 192.168.1.7
18 100010104 2012/3/7 17:48:55 0
19 300010102 2012/3/7 18:48:07 0
20 100010102 2012/3/7 19:07:54 0按照上面2楼朋友的sql:
SELECT SQL_CALC_FOUND_ROWS ROLE_ID, SUM(UNIX_TIMESTAMP(LOGIN_TIME)) AS logout_stamp FROM im_log_head.`t_log_login` WHERE is_login_in = 0 AND date(login_time)= date('2012-03-07') GROUP BY ROLE_ID ORDER BY ROLE_ID;
查询得到以下结果:
100010101 2012/3/7 16:42:06 2012/3/7 16:42:44
100010101 2012/3/7 16:43:12 2012/3/7 16:44:18
100010102 2012/3/7 16:40:42 2012/3/7 16:42:44
100010102 2012/3/7 16:54:32 2012/3/7 16:55:08
100010102 2012/3/7 17:15:48 2012/3/7 17:48:55
100010103 2012/3/7 16:55:51 2012/3/7 16:56:20
100010104 2012/3/7 16:44:24 2012/3/7 16:53:05
300010102 2012/3/7 17:05:08 2012/3/7 17:48:55
400010101 2012/3/7 16:55:40 2012/3/7 16:55:49
500010102 2012/3/7 16:53:47 2012/3/7 16:54:29
发现第三条记录:100010102 2012/3/7 16:40:42 2012/3/7 16:42:44
后面的登出时间应该是2012-03-07 16:53:05
第五条记录后面登出时间应该是2012-03-07 19:07:54
所以好像sql语句上还存在问题啊!!!