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 行受影响)
*/
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 行受影响)
*/
解决方案 »
- 数据库备份还原时遇到的问题
- sql 语句,求在表A 中的不在表B中的纪录,怎么写效率高?
- 简单问题,解决就揭帖
- 求个很简单SQL。select col3?col1:col2 根据一个列判断取哪个列?
- 求一个函数,clone(表\视图\返回单个表的存储过程),克隆一个表,不复制数据。得到一个结构一样的新表,空数据。
- 将excel导入到sql server,提示:OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。如何解决??
- 应该是简单的问题吧,可惜我不会。快来抢吧
- Oracle往SQLServer导入数据的难题?
- union 问题,很简单,但不知如何搞订 急阿!
- 关于数据导入的问题
- 新手请教:这段代码该如何改?
- SQL SERVER2000数据库恢复问题
declare @旅行记录表 table([姓名] nvarchar(2),[日期] nvarchar(10),[地址1] nvarchar(2),[地址2] nvarchar(2),[编号(主键)] int)
Insert @旅行记录表
select N'张三','2007-01-01',N'上海',N'广州',1 union all
select N'李四','2007-01-01',N'上海',N'广州',2 union all
select N'李四','2007-02-01',N'上海',N'成都',3 union all
select N'张三','2007-01-15',N'广州',N'上海',4 union all
select N'张三','2007-02-06',N'上海',N'广州',5 union all
select N'张三','2007-02-18',N'广州',N'上海',6Select [姓名],
row_number() over (partition by [姓名]order by [日期]) as ID,
case (row_number() over (partition by [姓名]order by [日期]))%2 when 1 then [日期] else '' end as [日期],
case (row_number() over (partition by [姓名]order by [日期]))%2 when 1 then [地址1] else '' end as [地址1],
case (row_number() over (partition by [姓名]order by [日期]))%2 when 1 then [地址2] else '' end as [地址2],
case (row_number() over (partition by [姓名]order by [日期]))%2 when 0 then [日期] else '' end as [日期2],
case (row_number() over (partition by [姓名]order by [日期]))%2 when 0 then [地址1] else '' end as [地址21],
case (row_number() over (partition by [姓名]order by [日期]))%2 when 0 then [地址2] else '' end as [地址22]
INTO #
from @旅行记录表SELECT [姓名],max([日期]) ,max([地址1]),max([地址2]),max([日期2]),max([地址21]),max([地址22]) from #
group by [姓名] ,(id-1)/2
/*
李四 2007-01-01 上海 广州 2007-02-01 上海 成都
张三 2007-01-01 上海 广州 2007-01-15 广州 上海
张三 2007-02-06 上海 广州 2007-02-18 广州 上海
*/
你二楼那种方式我也想过,
只是如果旅行还是在途状态,也就是说没有返回记录的话,
貌似会被过滤掉