请大家帮忙， 高分求助一个复杂的SQL统计语句怎么写呢？
是关于刷卡， 根据刷卡纪录求工时的统计语句：
表格如下：
sitenamedeptssnor_datein_timeout_time
All85000162006-06-272006-6-27 8:00
All85000162006-06-272006-6-27 8:01
All85000162006-06-272006-6-27 8:18
All85000162006-06-272006-6-27 8:19
All85000162006-06-272006-6-27 10:23
All85000162006-06-272006-6-27 10:31
All85000162006-06-272006-6-27 12:03
All85000162006-06-272006-6-27 12:06
All85000162006-06-272006-6-27 12:44
All85000162006-06-272006-6-27 12:54
All85000162006-06-272006-6-27 12:55
All85000162006-06-272006-6-27 13:44
All85000162006-06-272006-6-27 17:31
All85000162006-06-272006-6-27 17:34
All85000162006-06-302006-6-30 8:33
All85000162006-06-302006-6-30 9:33
All85000162006-06-302006-6-30 10:43
All85000162006-06-302006-6-30 11:54
All85000162006-06-302006-6-30 12:53
All85000162006-06-302006-6-30 15:03
All85000162006-06-302006-6-30 16:58
All85000162006-06-302006-6-30 17:25
All85000162006-07-072006-7-7 8:13
All85000162006-07-072006-7-7 10:33
All85000162006-07-072006-7-7 11:51
All85000162006-07-072006-7-7 13:27
All85000162006-07-072006-7-7 13:27
All85000162006-07-072006-7-7 13:28
All85000162006-07-072006-7-7 16:31
All85000162006-07-072006-7-7 16:59
All85000162006-07-072006-7-7 17:17
现在想统计对于同一天的，同一场区内的员工的刷卡纪录，
首先去除，只有刷进入和只有刷出去的纪录。
对于有连续时间刷进入的纪录，取最小的进入时间，
对于有连续时间刷出去的纪录，取最大的出去时间， 取出这个样子的时间后，把数据合并到一行里面去
而并不是要取一天之内的最小的进入时间和最大的出去时间
这个要得到这个样子的结果：
sitenamedeptssnor_datein_timeout_time
All85000162006-06-27 2006-6-27 8:002006-6-27 10:31
All85000162006-06-27 2006-6-27 12:032006-6-27 12:44
All85000162006-06-27 2006-6-27 12:542006-6-27 17:34
All85000162006-06-30 2006-6-30 8:332006-6-30 11:54
All85000162006-06-30 2006-6-30 12:532006-6-30 17:25
All85000162006-07-07 2006-7-7 8:132006-7-7 16:31
All85000162006-07-07 2006-7-7 16:592006-7-7 17:17
语句应该怎么写好呢 ？
时间很紧急，
请大家帮我想想办法好吗？ 谢谢
[解决办法]
由于你的数据sitenamedeptssno三字段全部相同,我测试作了简化,只使用了ssno一个字段,而且只取了6-27和6-30数据测试
--测试数据
declare @t table (
ssnovarchar(10),
r_datedatetime,
in_timedatetime,
out_timedatetime
)
insert @t select
'00016 ', '2006-06-27 ', '2006-6-27 8:00 ',null
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 8:01 '
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 8:18 '
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 8:19 '
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 10:23 '
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 10:31 '
union all select
'00016 ', '2006-06-27 ', '2006-6-27 12:03 ',null
union all select
'00016 ', '2006-06-27 ', '2006-6-27 12:06 ',null
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 12:44 '
union all select
'00016 ', '2006-06-27 ', '2006-6-27 12:54 ',null
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 12:55 '
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 13:44 '
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 17:31 '
union all select
'00016 ', '2006-06-27 ',null, '2006-6-27 17:34 '
union all select
'00016 ', '2006-06-30 ', '2006-6-30 8:33 ',null
union all select
'00016 ', '2006-06-30 ',null, '2006-6-30 9:33 '
union all select
'00016 ', '2006-06-30 ',null, '2006-6-30 10:43 '
union all select
'00016 ', '2006-06-30 ',null, '2006-6-30 11:54 '
union all select
'00016 ', '2006-06-30 ', '2006-6-30 12:53 ',null
union all select
'00016 ', '2006-06-30 ',null, '2006-6-30 15:03 '
union all select
'00016 ', '2006-06-30 ',null, '2006-6-30 16:58 '
union all select
'00016 ', '2006-06-30 ',null, '2006-6-30 17:25 '
--找出符合要求in数据到临时表
select *
into #i
from @t a
where in_time is not null
and out_time is null
and not exists (
select 1 from @t b
where ssno=a.ssno
and r_date=a.r_date
and in_time is not null
and out_time is null
and in_time <a.in_time
and not exists (
select 1 from @t c
where ssno=b.ssno
and r_date=b.r_date
and in_time is null
and out_time is not null
and out_time <a.in_time
and out_time> b.in_time
)
)
--找出符合要求out数据到临时表
select *
into #o
from @t a
where in_time is null
and out_time is not null
and not exists (
select 1 from @t b
where ssno=a.ssno
and r_date=a.r_date
and in_time is null
and out_time is not null
and out_time <a.out_time
and not exists (
select 1 from @t c
where ssno=b.ssno
and r_date=b.r_date
and in_time is not null
and out_time is null
and in_time <a.out_time
and in_time> b.out_time
)
)
--查询结果
select i.ssno,i.r_date,i.in_time,o.out_time
from #i i,#o o
where i.ssno=o.ssno
and i.r_date=o.r_date
and i.in_time <o.out_time
and not exists (
select 1 from #i
where ssno=o.ssno
and r_date=o.r_date
and in_time <o.out_time
and in_time> i.in_time
)
and not exists (
select 1 from #o
where ssno=i.ssno
and r_date=i.r_date
and i.in_time <out_time
and out_time <o.out_time
)
--删除临时表
drop table #i,#o
--结果
ssno r_date in_time out_time
---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
00016 2006-06-27 00:00:00.000 2006-06-27 08:00:00.000 2006-06-27 08:01:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:03:00.000 2006-06-27 12:44:00.000
00016 2006-06-27 00:00:00.000 2006-06-27 12:54:00.000 2006-06-27 12:55:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 08:33:00.000 2006-06-30 09:33:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 12:53:00.000 2006-06-30 15:03:00.000
（所影响的行数为 5 行）
[解决办法]
--創建測試環境
Declare @T Table (
ssno varchar(10),
r_date datetime,
in_time datetime,
out_time datetime)
--插入數據
Insert @T Select
'00016 ', '2006-06-27 ', '2006-6-27 8:00 ',NULL
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 8:01 '
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 8:18 '
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 8:19 '
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 10:23 '
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 10:31 '
Union All Select
'00016 ', '2006-06-27 ', '2006-6-27 12:03 ',NULL
Union All Select
'00016 ', '2006-06-27 ', '2006-6-27 12:06 ',NULL
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 12:44 '
Union All Select
'00016 ', '2006-06-27 ', '2006-6-27 12:54 ',NULL
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 12:55 '
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 13:44 '
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 17:31 '
Union All Select
'00016 ', '2006-06-27 ',NULL, '2006-6-27 17:34 '
Union All Select
'00016 ', '2006-06-30 ', '2006-6-30 8:33 ',NULL
Union All Select
'00016 ', '2006-06-30 ',NULL, '2006-6-30 9:33 '
Union All Select
'00016 ', '2006-06-30 ',NULL, '2006-6-30 10:43 '
Union All Select
'00016 ', '2006-06-30 ',NULL, '2006-6-30 11:54 '
Union All Select
'00016 ', '2006-06-30 ', '2006-6-30 12:53 ',NULL
Union All Select
'00016 ', '2006-06-30 ',NULL, '2006-6-30 15:03 '
Union All Select
'00016 ', '2006-06-30 ',NULL, '2006-6-30 16:58 '
Union All Select
'00016 ', '2006-06-30 ',NULL, '2006-6-30 17:25 '
--測試
--將表中數據假如自增列導入到臨時表中
Select ID = Identity(Int, 1, 1), * Into #T From @T Order By r_date, IsNull(in_time, out_time)
--刪除重復數據
Delete A From #T A
Where (Exists(Select in_time From #T Where ID = A.ID - 1 And out_time Is Null)
And out_time Is Null)
Or (Exists(Select in_time From #T Where ID = A.ID + 1 And in_time Is Null)
And in_time Is Null)
--將刷卡數據一一對應
Select A.ssno, A.r_date, A.in_time, Min(B.out_time) As out_time
From #T A Inner Join #T B
On A.ID < B.ID
Where A.out_time Is Null
Group By A.ssno, A.r_date, A.in_time
--刪除臨時表
Drop Table #T
GO
--結果
/*
ssnor_datein_timeout_time
000162006-06-27 00:00:00.0002006-06-27 08:00:00.0002006-06-27 10:31:00.000
000162006-06-27 00:00:00.0002006-06-27 12:03:00.0002006-06-27 12:44:00.000
000162006-06-27 00:00:00.0002006-06-27 12:54:00.0002006-06-27 17:34:00.000
000162006-06-30 00:00:00.0002006-06-30 08:33:00.0002006-06-30 11:54:00.000
000162006-06-30 00:00:00.0002006-06-30 12:53:00.0002006-06-30 17:25:00.000
*/
