select a.name, a.starttime ,a.flowtype, min(b.flowtype)
from flow a left join flow b
on a.name = b.name and a.starttime<b.starttime
group by a.name, a.starttime ,a.flowtype
from flow a left join flow b
on a.name = b.name and a.starttime<b.starttime
group by a.name, a.starttime ,a.flowtype
from flow a left join (select * from flow where flowtype='跑进')b
on a.name = b.name and a.starttime<b.starttime
where a.name='兔子'
group by a.name, a.starttime ,a.flowtype
,a.starttime
,a.flowtype
into #aa
from
flow a
where
a.flowtype='跑进'select name
,b.starttime
,b.flowtype
into #bb
from
flow b
where
b.flowtype='跑出'
select
cc. name
,cc.starttime
,cc.flowtype
,dd.starttime
,dd.flowtype
from
#aa cc
inner join
#bb dd
on
cc.name=dd.name
drop table #aa
drop table #bb
在把兔子加上就好
insert @t
select N'兔子','2007-07-05 10:22:00',N'跑出' union all
select N'老虎','2007-07-05 11:22:00',N'跑进' union all
select N'兔子','2007-07-06 10:45:00',N'跑进'select name, out_time,out_flowtype,in_time,in_flowtype
from
(
select name,starttime out_time,flowtype out_flowtype
from @t
where charindex(N'出',flowtype)>0
) a,
(
select name inname,starttime in_time,flowtype in_flowtype
from @t
where charindex(N'进',flowtype)>0
) b
where a.name=b.inname----------------------------------------------------------------
name out_time out_flowtype in_time in_flowtype
---------- ----------------------- ------------ ----------------------- -----------
兔子 2007-07-05 10:22:00.000 跑出 2007-07-06 10:45:00.000 跑进(1 row(s) affected)
其实,这个表中同一个name对应着很多条“跑出”的纪录,我是想把每一条“跑出”的纪录与它所对应的最近的一次"进“的纪录合并起来。注意是"进“,包括”跑进“,”跳进“,”走进“
from(
select a.name, a.starttime ,a.flowtype, min(b.starttime) in_time
from flow a left join (select * from flow where flowtype like '%进')b
on a.name = b.name and a.starttime<b.starttime
where a.name='兔子'
group by a.name, a.starttime ,a.flowtype
) a
join flow c on a.name=c.name and a.in_time=c.in_time
declare @table table([name] nvarchar(10),starttime datetime,flowtype nvarchar(2))
insert @table
select N'兔子','2007-07-05 10:22:00',N'跑出' union all
select N'老虎','2007-07-05 11:22:00',N'跑进' union all
select N'兔子','2007-07-06 10:45:00',N'跑进' union all
select N'老虎','2007-07-06 12:00:00',N'走出' union all
select N'老虎','2007-07-06 12:40:00',N'跳进'select aa.name,aa.outtime,aa.out_put,aa.intime,bb.in_put
from
(select a.name,a.outtime,a.out_put,dateadd(minute,a.diff,a.outtime)as intime
from
(select OUT_PUT.name,OUT_PUT.outtime,OUT_PUT.out_put,
min(datediff(minute,OUT_PUT.outtime,IN_PUT.intime))as diff
from
(select name,starttime as outtime,flowtype as out_put
from @table
where flowtype in('走出','跑出','跳出'))OUT_PUT
inner join
(select name,starttime as intime,flowtype as in_put
from @table
where flowtype in('走进','跑进','跳进'))IN_PUTon OUT_PUT.name=IN_PUT.name AND OUT_PUT.outtime<IN_PUT.intime group by OUT_PUT.name,OUT_PUT.outtime,OUT_PUT.out_put)a )aa
inner join
(select name,starttime as intime,flowtype as in_put
from @table
where flowtype in('走进','跑进','跳进'))bb
on aa.name=bb.name and aa.intime=bb.intime
create table 表1
([name] nvarchar(10),starttime datetime,flowtype nvarchar(2))
GO
insert 表1
select N'兔子','2007-07-05 10:22:00',N'跑出' union all
select N'老虎','2007-07-05 11:22:00',N'跑进' union all
select N'兔子','2007-07-06 10:45:00',N'跑进' union all
select N'老虎','2007-07-06 12:00:00',N'跑出' union all
select N'老虎','2007-07-06 12:40:00',N'跳进'
GO
select * from 表1
GO--测试
select a.*,b.starttime endtime,b.flowtype flowtypb,b.starttime-a.starttime timecout,IDENTITY(int,1,1) ID into #
from 表1 a inner join 表1 b on a.NAME=B.NAME and b.starttime-a.starttime>='1900-01-01 00:00:00.000'
AND A.FLOWTYPE='跑出' AND B.FLOWTYPE LIKE '%进'select a.* from # a inner join
(select name,starttime,min(timecout) timecout from # group by name,starttime
) as b on a.name=b.name and a.starttime=b.starttime and a.timecout=b.timecout
所以我们选择要查询出动物的出和出的时间,再根据出和出的时间去寻找时间最近的进。
如果进与出是相同记录数,而一比一对应,那么就比较好办:
select name,starttime,flowtype,
(select min(starttime) from flow f where right(flowtype,1)='进' and f.starttime>=a.starttime and f.name=a.name)endtime,
left(flowtype,1)+'进' endflowtype
from
(select name,starttime,flowtype from flow where right(flowtype,1)='出') a我都在SQL SERVER里面建表试过了。绝对可行,不给分,就跟小芳死过至于进出不是一比一的对应关系,估计写法会复杂一点,相信小芳自己想想就会明白的。
([name] nvarchar(10),starttime datetime,flowtype nvarchar(2))
GO
insert flow
select N'兔子','2007-07-05 10:22:00',N'跑出' union all
select N'老虎','2007-07-05 11:22:00',N'跑进' union all
select N'兔子','2007-07-06 10:45:00',N'跑进' union all
select N'老虎','2007-07-06 12:00:00',N'跑出' union all
select N'老虎','2007-07-06 12:40:00',N'跳进'
GO
select * from flow
GOselect a.name,a.starttime ,a.flowtype,
(select top 1 starttime from flow where name=a.name and flowtype like '%进' and starttime > a.starttime order by starttime),
(select top 1 flowtype from flow where name=a.name and flowtype like '%进' and starttime > a.starttime order by starttime)
from flow a where a.flowtype = '跑出'