如何从sql server获取第一次登录和最后一次注销时间? [英] How to get first login and last logout time from sql server?

问题描述

我的表数据是这样的..

My table Data like this..

ID         Name       Date                     Time       Type

 1         aaa      2014-07-01 00:00:00:000    9:35:43     In
 1         aaa      2014-07-01 00:00:00:000    11:10:43    OUT
 1         aaa      2014-07-01 00:00:00:000    11:35:43    In
 1         aaa      2014-07-01 00:00:00:000    1:00:43     OUT
 1         aaa      2014-07-01 00:00:00:000    1:35:43     In
 1         aaa      2014-07-01 00:00:00:000    4:00:43     OUT
 1         aaa      2014-07-01 00:00:00:000    4:10:43     In        
 1         aaa      2014-07-01 00:00:00:000    6:35:43     OUT

 2         Baa      2014-07-01 00:00:00:000    9:35:43     In
 2         Baa      2014-07-01 00:00:00:000    11:10:43    OUT
 2         Baa      2014-07-01 00:00:00:000    11:35:43    In
 2         Baa      2014-07-01 00:00:00:000    1:00:43     OUT
 2         Baa      2014-07-01 00:00:00:000    1:35:43     In
 2         Baa      2014-07-01 00:00:00:000    4:00:43     OUT
 2         Baa      2014-07-01 00:00:00:000    4:10:43     In        
 2         Baa      2014-07-01 00:00:00:000    6:35:43     OUT

现在我只想使用sql从表中获取先入后出详细信息.我怎样才能得到请帮帮我

now i want to get only first in And last out detalis from the table using sql. how can i get please help me

推荐答案

试试这个:

select * 
  from <YOUR_TABLE_HERE> ttt
  join (
      select max(time) logout_time, name, date
        from <YOUR_TABLE_HERE>
       where type = 'Out'
       group by name, date) max_date_table on ttt.name = max_date_table.name and ttt.date = max_date_table.date and max_date_table.logout_time = ttt.time
  join (
      select min(time) login_time, name, date
        from <YOUR_TABLE_HERE>
       where type = 'In'
       group by name, date) min_date_table on ttt.name = min_date_table.name and ttt.date = min_date_table.date and ttt.time = min_date_table.login_time
order by name

这篇关于如何从sql server获取第一次登录和最后一次注销时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！