求SQL语句

Question

with tb(a,b)as(
select '20140101 12:01:01','进' union
select '20140101 12:05:05','出' union
select '20140101 12:06:08','进' union
select '20140101 12:07:15','出' union
select '20140101 12:11:20','进' union
select '20140101 12:15:40','进' union
select '20140101 12:20:44','出')
,tc as(
select ROW_NUMBER() over(order by a)num,* from tb
)
select a1.a 日期1,a2.a 日期2,DATEDIFF(minute,a1.a,a2.a) 时间差
from (select num,a from tc where b='进')a1 full join
     (select num,a from tc where b='出')a2 on a1.num=a2.num-1

Answer 1

/*------------------------;with cte as
(
select '20140101 12:01:01' as dt,'进' as flag union all
select '20140101 12:05:05' as dt,'出' as flag union all
select '20140101 12:06:08' as dt,'进' as flag union all
select '20140101 12:07:15' as dt,'出' as flag union all
select '20140101 12:11:20' as dt,'进' as flag union all
select '20140101 12:15:40' as dt,'进' as flag union all
select '20140101 12:20:44' as dt,'出' as flag
),
cte1 as
(
select *,ROW_NUMBER()over(order by dt,flag) as N
from cte
)
select A.dt as '时间1',B.dt as '时间2',DATEDIFF(MINUTE,A.dt,B.dt) as '时间差'
from (select * from cte1 where flag='进') as A
left join (select * from cte1 where flag='出') as B on A.N=B.N-1;
------------------------*/
时间1               时间2               时间差
----------------- ----------------- -----------
20140101 12:01:01 20140101 12:05:05 4
20140101 12:06:08 20140101 12:07:15 1
20140101 12:11:20 NULL              NULL
20140101 12:15:40 20140101 12:20:44 5(4 行受影响)

Answer 2

/*
表的数据是：
 日期                                       状态 最终想要的结果表是： 日期1                                           日期2                                  时间差
20140101 12:01:01         20140101 12:05:05                    4
 20140101 12:06:08          20140101 12:07:15                    1
 20140101 12:11:20          null                                                  
 20140101 12:15:40         20140101 12:20:44                       5  */
 if object_id('b')is not null drop table b
 go
 create table b(a datetime2(0),state varchar(2))
 go
 insert into b (a,state)
             values('20140101 12:01:01','进'),
('20140101 12:05:05','出'),
('20140101 12:06:08','进'),
('20140101 12:07:15','出'),
('20140101 12:11:20','进'),
('20140101 12:15:40','进'),
('20140101 12:20:44','出')select * from b
--表展示
/*
a                           state
--------------------------- -----
2014-01-01 12:01:01         进
2014-01-01 12:05:05         出
2014-01-01 12:06:08         进
2014-01-01 12:07:15         出
2014-01-01 12:11:20         进
2014-01-01 12:15:40         进
2014-01-01 12:20:44         出(7 行受影响)
*/
--插入c表
select *,row_number() over(order by a) into c from bselect c.a as [日期1],b.a as [日期2],datediff(minute,c.a,b.a) as [时间差（分）] from c left outer join  c as b on c.rn = b.rn-1 and b.state ='出'
where c.state ='进'--结果
/*
日期1                         日期2                         时间差（分）
--------------------------- --------------------------- -----------
2014-01-01 12:01:01         2014-01-01 12:05:05         4
2014-01-01 12:06:08         2014-01-01 12:07:15         1
2014-01-01 12:11:20         NULL                        NULL
2014-01-01 12:15:40         2014-01-01 12:20:44         5(4 行受影响)
*/

Answer 3

SELECT MIN(a.日期) AS 日期1, MIN(b.日期) AS 日期2
FROM TEST a
INNER JOIN TEST b
ON a.日期 < b.日期 AND a.状态 = '进' AND b.状态 = '出'
GROUP BY a.日期

Answer 4

select *,row_number() over(order by a) into c from b
 
select c.a as [日期1],b.a as [日期2],datediff(minute,c.a,b.a) as [时间差（分）] from c left outer join  c as b on c.rn = b.rn-1 and b.state ='出'
where c.state ='进'