请大家帮忙， 高分求助一个复杂的SQL统计语句怎么写呢？

[wscft]

是关于刷卡， 根据刷卡纪录求工时的统计语句：
表格如下：
sitename dept ssno r_date in_time out_time
All 85 00016 2006-06-27 2006-6-27 8:00
All 85 00016 2006-06-27 2006-6-27 8:01
All 85 00016 2006-06-27 2006-6-27 8:18
All 85 00016 2006-06-27 2006-6-27 8:19
All 85 00016 2006-06-27 2006-6-27 10:23
All 85 00016 2006-06-27 2006-6-27 10:31
All 85 00016 2006-06-27 2006-6-27 12:03
All 85 00016 2006-06-27 2006-6-27 12:06
All 85 00016 2006-06-27 2006-6-27 12:44
All 85 00016 2006-06-27 2006-6-27 12:54
All 85 00016 2006-06-27 2006-6-27 12:55
All 85 00016 2006-06-27 2006-6-27 13:44
All 85 00016 2006-06-27 2006-6-27 17:31
All 85 00016 2006-06-27 2006-6-27 17:34
All 85 00016 2006-06-30 2006-6-30 8:33
All 85 00016 2006-06-30 2006-6-30 9:33
All 85 00016 2006-06-30 2006-6-30 10:43
All 85 00016 2006-06-30 2006-6-30 11:54
All 85 00016 2006-06-30 2006-6-30 12:53
All 85 00016 2006-06-30 2006-6-30 15:03
All 85 00016 2006-06-30 2006-6-30 16:58
All 85 00016 2006-06-30 2006-6-30 17:25
All 85 00016 2006-07-07 2006-7-7 8:13
All 85 00016 2006-07-07 2006-7-7 10:33
All 85 00016 2006-07-07 2006-7-7 11:51
All 85 00016 2006-07-07 2006-7-7 13:27
All 85 00016 2006-07-07 2006-7-7 13:27
All 85 00016 2006-07-07 2006-7-7 13:28
All 85 00016 2006-07-07 2006-7-7 16:31
All 85 00016 2006-07-07 2006-7-7 16:59
All 85 00016 2006-07-07 2006-7-7 17:17

现在想统计对于同一天的，同一场区内的员工的刷卡纪录，
首先去除，只有刷进入和只有刷出去的纪录。
对于有连续时间刷进入的纪录，取最小的进入时间，
对于有连续时间刷出去的纪录，取最大的出去时间， 取出这个样子的时间后，把数据合并到一行里面去
而并不是要取一天之内的最小的进入时间和最大的出去时间
这个要得到这个样子的结果：
sitename dept ssno r_date in_time out_time
All 85 00016 2006-06-27 2006-6-27 8:00 2006-6-27 10:31
All 85 00016 2006-06-27 2006-6-27 12:03 2006-6-27 12:44
All 85 00016 2006-06-27 2006-6-27 12:54 2006-6-27 17:34
All 85 00016 2006-06-30 2006-6-30 8:33 2006-6-30 11:54
All 85 00016 2006-06-30 2006-6-30 12:53 2006-6-30 17:25
All 85 00016 2006-07-07 2006-7-7 8:13 2006-7-7 16:31
All 85 00016 2006-07-07 2006-7-7 16:59 2006-7-7 17:17

语句应该怎么写好呢 ？
时间很紧急，
请大家帮我想想办法好吗？ 谢谢

[wscft]

还有没有更高效一点的算法呢，　楼上给出的这种算法，　效率也不是很高的　哦

[qqhmitzk]

第八问:生成18位的编号　改一下

CREATE VIEW v_GetDate
AS
SELECT dt=replace(left(Convert(varchar,GETDATE(),120),10),'-','')
GO


CREATE FUNCTION create_BH()
RETURNS char(18)
AS
BEGIN
DECLARE @dt CHAR(8)
SELECT @dt=dt FROM v_GetDate
RETURN(SELECT 'scrw'+@dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM SCRW WITH(XLOCK,PAGLOCK)
WHERE BH like 'scrw'+@dt+'%')
END
GO


CREATE TABLE SCRW (
BH char(18) PRIMARY KEY DEFAULT dbo.create_BH(),
li int)
go
INSERT SCRW (li) VALUES(1)
INSERT SCRW (li) VALUES(2)
INSERT SCRW (li) VALUES(3)
go
select * from SCRW
--结果

BH li
------------------ -----------
scrw20070727000001 1
scrw20070727000002 2
scrw20070727000003 3

（所影响的行数为 3 行）

[qqhmitzk]

我晕,发错拉

[wscft]

各位帮帮忙啊 ？

[昵称被占用了]

有点太复杂

[herozwh1979]

和我的问题差不多，我提的问题里有相似代码，但是我需要高效啊，，，，，怎么做。。。

[wscft]

to:herozwh1979() , 你的是什么问题呢？ 我也想很高效的查询出数据来啊
但是， 象我这个问题，除了用游标一行一行的取出来，比较上下两行的值（这样速度太慢了啊） 就没有什么更好的办法了吗？

[昵称被占用了]

由于你的数据sitename dept ssno三字段全部相同,我测试作了简化,只使用了ssno一个字段,而且只取了6-27和6-30数据测试

--测试数据
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'

--找出符合要求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 行）

[昵称被占用了]

去掉的字段加回来简单只要把形如
ssno=a.ssno
and
的语句改成
sitename=a.sitename
and dept=a.dept
and ssno=a.ssno
and

把形如
i.ssno=o.ssno
and
的语句改成
i.sitename=o.sitename
and i.dept=o.dept
and i.ssno=o.ssno
and

[昵称被占用了]

out有错误，改下


--测试数据
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'

--找出符合要求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 10:31: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 17:34:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 08:33:00.000 2006-06-30 11:54:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 12:53:00.000 2006-06-30 17:25:00.000

（所影响的行数为 5 行）

[herozwh1979]

^_^，有人帮我把问题解决了，回答你问题的是高手啊，你的也解决了。

[wgqqgw]

复杂了……
还是尽量少让这种事情发生是上策。

[paoluo]

借助Haiwer(海阔天空)的數據

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 Left Join #T B
On A.ID < B.ID
Where A.out_time Is Null
Group By A.ssno, A.r_date, A.in_time

[paoluo]

--創建測試環境
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
--結果
/*
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 10:31: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 17:34:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 08:33:00.000 2006-06-30 11:54:00.000
00016 2006-06-30 00:00:00.000 2006-06-30 12:53:00.000 2006-06-30 17:25:00.000
*/

[wscft]

十分感谢楼上提供的算法，速度很快， 效率也高 谢谢