# 请大家帮忙， 高分一个复杂的SQL统计语句如何写呢(2)

www.MyException.Cn  网友分享于：2013-02-14  浏览：16次

'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