sql处理进出判断选取

[LLT_NINE]

①可以查询不同人员的不同日期的进出
②去除第一笔进与最后一笔出

[道素]

你给的例子数据有问题吧，按照左边的数据，去除第一笔进与最后一笔出，还剩七笔啊

CREATE TABLE #tab(p NVARCHAR(10),d DATETIME,E NVARCHAR(10))
INSERT INTO #tab(p,D,E)
SELECT N'张三','2017/06/15 0:30','I01' union all
SELECT N'张三','2017/06/15 0:30','o01' union all
SELECT N'张三','2017/06/15 1:26','o01' union all
SELECT N'张三','2017/06/15 2:20','I01' union all
SELECT N'张三','2017/06/15 2:27','I01' union all
SELECT N'张三','2017/06/15 6:30','o01' union all
SELECT N'张三','2017/06/15 6:30','I01' union all
SELECT N'张三','2017/06/15 19:26','I01' union all
SELECT N'张三','2017/06/15 19:28','o01'  
select * from (
    select *
      ,row_number()over(partition by left(e,1) order by d) as seq,count(0)over(partition by left(e,1) ) as cnt
    from #tab
) as t where (left(e,1)='I' and seq>1) or (left(e,1)='o' and seq!=cnt)

  	p	d	E	seq	cnt
1	张三	15.06.2017 02:20:00	I01	2	5
2	张三	15.06.2017 02:27:00	I01	3	5
3	张三	15.06.2017 06:30:00	I01	4	5
4	张三	15.06.2017 19:26:00	I01	5	5
5	张三	15.06.2017 00:30:00	o01	1	4
6	张三	15.06.2017 01:26:00	o01	2	4
7	张三	15.06.2017 06:30:00	o01	3	4

上面结果再进行动态转置就行，但列又是按照什么顺序呢? 下面是一个转置的例子，不知道是不是你要的

declare @cols nvarchar(max),@sql nvarchar(max)

    select  @cols=isnull(@cols+N',',N'')+quotename(N'出(O设备)'+ltrim(a.seq))+N','+quotename(N'入(I设备)'+ltrim(a.seq)) from ( 
       select distinct row_number()over(partition by p,left(e,1) order by d) as seq,count(0)over(partition by p,left(e,1) ) as cnt from #tab 
    ) as a where a.seq!=a.cnt group by a.seq   
select @cols

set @sql=N'
select * from (
    select t.p,t.d,ltrim(case when left(e,1)=''I'' then N''入(I设备)''+ltrim(t.seq-1) else N''出(O设备)''+ltrim(t.seq) end) as col
    from (
       select *
          ,row_number()over(partition by p,left(e,1) order by d) as seq,count(0)over(partition by p,left(e,1) ) as cnt
       from #tab
    ) as t where (left(e,1)=''I'' and seq>1) or (left(e,1)=''o'' and seq!=cnt)
) as tt pivot(max(d) for col in ('+@cols+N')) p'
exec(@sql)

  	p	出(O设备)1	入(I设备)1	出(O设备)2	入(I设备)2	出(O设备)3	入(I设备)3	出(O设备)4	入(I设备)4
1	张三	15.06.2017 00:30:00	15.06.2017 02:20:00	15.06.2017 01:26:00	15.06.2017 02:27:00	15.06.2017 06:30:00	15.06.2017 06:30:00	NULL	15.06.2017 19:26:00