返程问题,找出雇员从本地出发后直接返回的情况

 --返程问题,找出雇员从本地出发后直接返回的情况
create table trav(name nvarchar(10),date datetime,comefrom nvarchar(10),destin nvarchar(10),id int)
insert into trav select '张三','2007-01-01','上海','广州',1
insert into trav select '李四','2007-01-01','上海','广州',2
insert into trav select '李四','2007-02-01','上海','成都',3
insert into trav select '张三','2007-01-15','广州','上海',4
insert into trav select '张三','2007-02-06','上海','广州',5
insert into trav select '张三','2007-02-18','广州','上海',6
go
select a.name,a.date,a.comefrom,a.destin,b.date,b.comefrom,b.destin
from trav a inner join trav b on a.name=b.name and a.comefrom=b.destin and a.destin=b.comefrom where a.id<b.id 
and not exists(select 1 from trav where comefrom=b.comefrom and date<b.date and date>a.date)
go
drop table trav
/*
name       date                    comefrom   destin     date                    comefrom   destin
---------- ----------------------- ---------- ---------- ----------------------- ---------- ----------
张三         2007-01-01 00:00:00.000 上海         广州         2007-01-15 00:00:00.000 广州         上海
张三         2007-02-06 00:00:00.000 上海         广州         2007-02-18 00:00:00.000 广州         上海

(2 行受影响)
*/