SQL语句

Question

Userid CheckTime        state 
10001 2009-1-1 7:26:00    i          
10001 2009-1-1 11:35:00    o    
10001 2009-1-1 11:55:00    i      
10001 2009-1-1 17:27:00    o 
10001 2009-1-2 7:26:00    i                
10001 2009-1-2 11:55:00    o      
10001 2009-1-2 17:27:00    o 
10001 2009-1-3 7:26:00    i                
10001 2009-1-3 11:55:00    i    
i(表示上班)  o（表示下班） 
一天四次打卡 也可能3次2次1次，上午归上午，下午归下午（上午第一次打卡就是i,第二次打卡就是o，下午同理，上下时间分界为11：50）,根据checkTime的时间计算出i或o, 
    

Answer 1

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Userid] int,[CheckTime] datetime,[state] varchar(5))
insert [tb]
select 10001,'2009-1-1 7:26:00',null union all
select 10001,'2009-1-1 11:35:00',null union all
select 10001,'2009-1-1 11:55:00',null union all
select 10001,'2009-1-1 17:27:00',null union all
select 10001,'2009-1-2 7:26:00',null union all
select 10001,'2009-1-2 11:55:00',null union all
select 10001,'2009-1-2 17:27:00',null union all
select 10001,'2009-1-3 7:26:00',null union all
select 10001,'2009-1-3 11:55:00',null
 
---查询---select 
  Userid,
  CheckTime,
  ampm=case when convert(varchar(8),checktime,108)<'11:50:00' then 'am' else 'pm' end
into #
from [tb]select
  Userid,
  CheckTime,  
  state=case when not exists(select 1 from # where datediff(day,CheckTime,t.CheckTime)=0 and ampm=t.ampm and checktime<t.checktime) then 'i' else 'o' end
from # t
---结果---
Userid      CheckTime                                              state 
----------- ------------------------------------------------------ ----- 
10001       2009-01-01 07:26:00.000                                i
10001       2009-01-01 11:35:00.000                                o
10001       2009-01-01 11:55:00.000                                i
10001       2009-01-01 17:27:00.000                                o
10001       2009-01-02 07:26:00.000                                i
10001       2009-01-02 11:55:00.000                                i
10001       2009-01-02 17:27:00.000                                o
10001       2009-01-03 07:26:00.000                                i
10001       2009-01-03 11:55:00.000                                i（所影响的行数为 9 行）

Answer 2

Userid CheckTime        state 
10001 2009-1-1 7:26:00    i          
10001 2009-1-1 11:35:00    o    
10001 2009-1-1 11:55:00    i      
10001 2009-1-1 17:27:00    o 
10001 2009-1-2 7:26:00    i                
10001 2009-1-2 11:55:00    o      
10001 2009-1-2 17:27:00    o 
10001 2009-1-3 7:26:00    i                
10001 2009-1-3 11:55:00    i    
--select *
from ta a
where not exists(select 1 from ta where userid= a.userid and datediff(d,checktime,a.checktime) = 0 and state = 'i' and checktime < a.checktime) and state = 'i'
union all
select *
from ta a
where not exists(select 1 from ta where userid= a.userid and datediff(d,checktime,a.checktime) = 0 and state = '0' and checktime < a.checktime) and state = '0'