请教一条SQL语句

[yucong]

[名称] [时间] [状态]

汽车A 2001-1-1 1:1:1 出发
汽车A 2001-1-1 2:2:2 返回
汽车B 2001-1-1 3:3:3 出发
汽车C 2001-1-1 5:5:5 出发
汽车B 2001-1-1 4:4:4 返回
汽车C 2001-1-1 6:6:6 返回
汽车D 2001-1-1 7:7:7 出发
汽车A 2001-1-1 8:8:8 出发
汽车A 2001-1-1 9:9:9 返回


通过一条SQL查询得到以下结果
注意需要考虑同一辆汽车的多次出发和返回的情况

[名称] [出发时间] [返回时间]

汽车A 2001-1-1 1:1:1 2001-1-1 2:2:2
汽车B 2001-1-1 3:3:3 2001-1-1 4:4:4
汽车C 2001-1-1 5:5:5 2001-1-1 6:6:6
汽车D 2001-1-1 7:7:7 null
汽车A 2001-1-1 8:8:8 2001-1-1 9:9:9

多谢执教

[you_tube]

-->生成测试数据
declare @Tab table(
[名称]  varchar(10),  [时间]    datetime,              [状态] varchar(10))

insert into @Tab select '汽车A',    '2001-1-1 1:1:1',        '出发'
insert into @Tab select '汽车A',    '2001-1-1 2:2:2',        '返回'
insert into @Tab select '汽车B',    '2001-1-1 3:3:3',        '出发'
insert into @Tab select '汽车C',    '2001-1-1 5:5:5',        '出发'
insert into @Tab select '汽车B',    '2001-1-1 4:4:4',        '返回'
insert into @Tab select '汽车C',    '2001-1-1 6:6:6',        '返回'
insert into @Tab select '汽车D',    '2001-1-1 7:7:7',        '出发'
insert into @Tab select '汽车A',    '2001-1-1 8:8:8',        '出发'
insert into @Tab select '汽车A',    '2001-1-1 9:9:9',        '返回'

-->生成测试环境
select a.[名称], 
a.[时间] as [出发时间], 
b.[时间] as [返回时间] 
from  
(select [名称],[时间],ROW_NUMBER() OVER (PARTITION BY [名称] ORDER BY [名称]) Num from @Tab where [状态]='出发'  
) a 
left join 
(select [名称],[时间],ROW_NUMBER() OVER (PARTITION BY [名称] ORDER BY [名称]) Num from @Tab where [状态]='返回' 
) b 
on a.[名称]=b.[名称] AND a.Num = b.Num

-->测试结果
名称         出发时间                    返回时间
---------- ----------------------- -----------------------
汽车A        2001-01-01 01:01:01.000 2001-01-01 02:02:02.000
汽车A        2001-01-01 08:08:08.000 2001-01-01 09:09:09.000
汽车B        2001-01-01 03:03:03.000 2001-01-01 04:04:04.000
汽车C        2001-01-01 05:05:05.000 2001-01-01 06:06:06.000
汽车D        2001-01-01 07:07:07.000 NULL

[you_tube]

来个CET的

-->生成测试数据
declare @Tab table(
[名称]  varchar(10),  [时间]    datetime,              [状态] varchar(10))

insert into @Tab select '汽车A',    '2001-1-1 1:1:1',        '出发'
insert into @Tab select '汽车A',    '2001-1-1 2:2:2',        '返回'
insert into @Tab select '汽车B',    '2001-1-1 3:3:3',        '出发'
insert into @Tab select '汽车C',    '2001-1-1 5:5:5',        '出发'
insert into @Tab select '汽车B',    '2001-1-1 4:4:4',        '返回'
insert into @Tab select '汽车C',    '2001-1-1 6:6:6',        '返回'
insert into @Tab select '汽车D',    '2001-1-1 7:7:7',        '出发'
insert into @Tab select '汽车A',    '2001-1-1 8:8:8',        '出发'
insert into @Tab select '汽车A',    '2001-1-1 9:9:9',        '返回'

-->生成测试环境

;WITH Args AS (
select a.[名称], 
a.[时间] as [出发时间], 
b.[时间] as [返回时间] 
from  
(select [名称],[时间],ROW_NUMBER() OVER (PARTITION BY [名称] ORDER BY [名称]) Num from @Tab where [状态]='出发'  
) a 
left join 
(select [名称],[时间],ROW_NUMBER() OVER (PARTITION BY [名称] ORDER BY [名称]) Num from @Tab where [状态]='返回' 
) b 
on a.[名称]=b.[名称] AND a.Num = b.Num)
SELECT * FROM Args ORDER BY [出发时间]

-->测试结果
名称         出发时间                    返回时间
---------- ----------------------- -----------------------
汽车A        2001-01-01 01:01:01.000 2001-01-01 02:02:02.000
汽车B        2001-01-01 03:03:03.000 2001-01-01 04:04:04.000
汽车C        2001-01-01 05:05:05.000 2001-01-01 06:06:06.000
汽车D        2001-01-01 07:07:07.000 NULL
汽车A        2001-01-01 08:08:08.000 2001-01-01 09:09:09.000

(5 行受影响)

[yucong]

对不起
工程用的是sql 2000没有ROW_NUMBER()函数阿

[you_tube]

[Quote=引用 10 楼 yucong 的回复:]
对不起
工程用的是sql 2000没有ROW_NUMBER()函数阿
[/Quote]
那就用树人的方法

[yucong]

树人的方法很不错但是如果有结束时间没有开始时间可能会有些问题
比如
[名称] [时间] [状态]
汽车A 2001-1-1 2:2:2 返回
汽车B 2001-1-1 3:3:3 出发
汽车C 2001-1-1 5:5:5 出发
汽车B 2001-1-1 4:4:4 返回
汽车C 2001-1-1 6:6:6 返回
汽车D 2001-1-1 7:7:7 出发
汽车A 2001-1-1 8:8:8 出发
汽车A 2001-1-1 9:9:9 返回

正确应该返回下面结果或者干脆丢弃第一行
[名称] [出发时间] [返回时间]
汽车A null 2001-1-1 2:2:2
汽车B 2001-1-1 3:3:3 2001-1-1 4:4:4
汽车C 2001-1-1 5:5:5 2001-1-1 6:6:6
汽车D 2001-1-1 7:7:7 null
汽车A 2001-1-1 8:8:8 2001-1-1 9:9:9

[百年树人]

[Quote=引用 12 楼 yucong 的回复:]
树人的方法很不错但是如果有结束时间没有开始时间可能会有些问题
比如
[名称] [时间] [状态]
汽车A 2001-1-1 2:2:2 返回
汽车B 2001-1-1 3:3:3 出发
汽车C 2001-1-1 5:5:5 出发
汽车B 2001-1-1 4:4:4 返回
汽车C 2001-1-1 6:6:6 返回
汽车D 2001-1-1 7:7:7 出发
汽车A 2001-1-1 8:8:8 出发
汽车A 2001-1-1 9…
[/Quote]
那就用full join

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([名称] varchar(5),[时间] datetime,[状态] varchar(4))
insert [tb]
select '汽车A','2001-1-1 2:2:2','返回' union all
select '汽车B','2001-1-1 3:3:3','出发' union all
select '汽车C','2001-1-1 5:5:5','出发' union all
select '汽车B','2001-1-1 4:4:4','返回' union all
select '汽车C','2001-1-1 6:6:6','返回' union all
select '汽车D','2001-1-1 7:7:7','出发' union all
select '汽车A','2001-1-1 8:8:8','出发' union all
select '汽车A','2001-1-1 9:9:9','返回'
 
---查询---
select 
  isnull(a.[名称],b.[名称]) as 名称,
  a.[时间]  as [出发时间],
  b.[时间]  as [返回时间]
from
  (select [名称],[时间],px=(select count(1)+1 from tb where [状态]='出发' and [名称]=t.[名称] and [时间]<t.[时间]) from tb t where [状态]='出发') a
full join 
  (select [名称],[时间],px=(select count(1)+1 from tb where [状态]='返回' and [名称]=t.[名称] and [时间]<t.[时间]) from tb t where [状态]='返回') b
on 
  a.[名称]=b.[名称] and a.px=b.px


---结果---
名称    出发时间                                                   返回时间                                                   
----- ------------------------------------------------------ ------------------------------------------------------ 
汽车A   2001-01-01 08:08:08.000                                2001-01-01 02:02:02.000
汽车B   2001-01-01 03:03:03.000                                2001-01-01 04:04:04.000
汽车C   2001-01-01 05:05:05.000                                2001-01-01 06:06:06.000
汽车A   NULL                                                   2001-01-01 09:09:09.000
汽车D   2001-01-01 07:07:07.000                                NULL

（所影响的行数为 5 行）

[yucong]

好像也不行
那样会把汽车A在2001-1-1 8:8:8的出发与2001-1-1 2:2:2的返回结合在一起

[yibey84]

select 名称,出发时间=时间,返回时间=(select top 1 时间 where 时间>a.时间and 名称=a.名称）  from tb a where a.状态='出发'

[ggtggt]

select min(名称),c.cf,min(c.fh)from
(select a.名称,a.时间 as cf ,b.时间 as fh from
(select 名称,时间,状态
from tb
where [状态]='出发')a
left join
(select 名称,时间,状态
from tb
where [状态]='返回')b
on a.名称=b.名称)c

group by c.cf;

[百年树人]

[Quote=引用 14 楼 yucong 的回复:]
好像也不行
那样会把汽车A在2001-1-1 8:8:8的出发与2001-1-1 2:2:2的返回结合在一起
[/Quote]

这样就不能用px了

---查询---
select 
  isnull(a.[名称],b.[名称]) as 名称,
  a.[时间]  as [出发时间],
  b.[时间]  as [返回时间]
from
  (select [名称],[时间] from tb t where [状态]='出发') a
full join 
  (select [名称],[时间] from tb t where [状态]='返回') b
on 
  a.[名称]=b.[名称] and a.时间<b.时间


---结果---
名名称    出发时间                                                   返回时间                                                   
----- ------------------------------------------------------ ------------------------------------------------------ 
汽车A   NULL                                                   2001-01-01 02:02:02.000
汽车B   2001-01-01 03:03:03.000                                2001-01-01 04:04:04.000
汽车C   2001-01-01 05:05:05.000                                2001-01-01 06:06:06.000
汽车A   2001-01-01 08:08:08.000                                2001-01-01 09:09:09.000
汽车D   2001-01-01 07:07:07.000                                NULL

（所影响的行数为 5 行）

[yucong]

但是如果不用px会出现一个出发时间出现多个记录对应不同的返回时间的情况

[yucong]

我感觉还是得用px 但是的判断一下如果没有出发时间只有返回时间的情况

[百年树人]

加了一行测试数据，再改了一下，试试--

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([名称] varchar(5),[时间] datetime,[状态] varchar(4))
insert [tb]
select '汽车A','2001-1-1 2:2:2','返回' union all
select '汽车B','2001-1-1 3:3:3','出发' union all
select '汽车C','2001-1-1 5:5:5','出发' union all
select '汽车B','2001-1-1 4:4:4','返回' union all
select '汽车C','2001-1-1 6:6:6','返回' union all
select '汽车D','2001-1-1 7:7:7','出发' union all
select '汽车A','2001-1-1 8:8:8','出发' union all
select '汽车A','2001-1-1 9:9:9','返回' union all
select '汽车A','2001-1-1 11:11:11','出发' union all
select '汽车A','2001-1-1 12:12:12','返回'
 
---查询---
select 
  isnull(a.[名称],b.[名称]) as 名称,
  a.[时间]  as [出发时间],
  b.[时间]  as [返回时间]
from
(
select 
  [名称],
  [时间],
   px=(case when (select min(时间) from tb where [状态]='返回' and [名称]=t.[名称])<t.[时间] then 1 else 0 end)
     +(select count(1)+1 from tb where [状态]='出发' and [名称]=t.[名称] and [时间]<t.[时间]) 
from 
  tb t 
where 
  [状态]='出发'
) a
full join 
(
select 
  [名称],
  [时间],
   px=(select count(1)+1 from tb where [状态]='返回' and [名称]=t.[名称] and [时间]<t.[时间]) 
from 
  tb t 
where 
  [状态]='返回'
) b
on
  a.[名称]=b.[名称] and a.px=b.px

/**

名称    出发时间                                                   返回时间                                                   
----- ------------------------------------------------------ ------------------------------------------------------ 
汽车B   2001-01-01 03:03:03.000                                2001-01-01 04:04:04.000
汽车C   2001-01-01 05:05:05.000                                2001-01-01 06:06:06.000
汽车D   2001-01-01 07:07:07.000                                NULL
汽车A   2001-01-01 08:08:08.000                                2001-01-01 09:09:09.000
汽车A   2001-01-01 11:11:11.000                                2001-01-01 12:12:12.000
汽车A   NULL                                                   2001-01-01 02:02:02.000

（所影响的行数为 6 行）

**/

[yucong]

好像还是有漏洞,有的记录组合不准确
本次的开始会和下一次的返回组合

[Hinco]

也给个写法，这个写法在连续多次出发没有返回时只算最早一次的出发时间

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([名称] varchar(5),[时间] datetime,[状态] varchar(4))
insert [tb]
select '汽车A','2001-1-1 1:1:1','出发' union all
select '汽车A','2001-1-1 2:2:2','返回' union all
select '汽车B','2001-1-1 3:3:3','出发' union all
select '汽车C','2001-1-1 5:5:5','出发' union all
select '汽车B','2001-1-1 4:4:4','返回' union all
select '汽车C','2001-1-1 6:6:6','返回' union all
select '汽车D','2001-1-1 7:7:7','出发' union all
select '汽车A','2001-1-1 8:8:8','出发' union all
select '汽车A','2001-1-1 9:9:9','返回'

select 名称,min(case when 状态='出发' then [时间] else null end) as 出发时间,
	max(case when 状态='返回' then [时间] else null end) as 返回时间
from (select 2*(select count(*) from tb where 名称=a.名称 and 时间<a.时间 and 状态='返回') as times,* from tb a) t
group by 名称,times

[Hinco]

补充：上面2* 没用，测试时加的，可以去掉

[xuezhanliang6886]

-->生成测试数据
declare @Tab table(
[名称] varchar(10), [时间] datetime, [状态] varchar(10))

insert into @Tab select '汽车A', '2001-1-1 1:1:1', '出发'
insert into @Tab select '汽车A', '2001-1-1 2:2:2', '返回'
insert into @Tab select '汽车B', '2001-1-1 3:3:3', '出发'
insert into @Tab select '汽车C', '2001-1-1 5:5:5', '出发'
insert into @Tab select '汽车B', '2001-1-1 4:4:4', '返回'
insert into @Tab select '汽车C', '2001-1-1 6:6:6', '返回'
insert into @Tab select '汽车D', '2001-1-1 7:7:7', '出发'
insert into @Tab select '汽车A', '2001-1-1 8:8:8', '出发'
insert into @Tab select '汽车A', '2001-1-1 9:9:9', '返回'

-->生成测试环境
select a.[名称],
a.[时间] as [出发时间],
b.[时间] as [返回时间]
from
(select [名称],[时间],ROW_NUMBER() OVER (PARTITION BY [名称] ORDER BY [名称]) Num from @Tab where [状态]='出发'
) a
left join
(select [名称],[时间],ROW_NUMBER() OVER (PARTITION BY [名称] ORDER BY [名称]) Num from @Tab where [状态]='返回'
) b
on a.[名称]=b.[名称] AND a.Num = b.Num

-->测试结果
名称 出发时间 返回时间
---------- ----------------------- -----------------------
汽车A 2001-01-01 01:01:01.000 2001-01-01 02:02:02.000
汽车A 2001-01-01 08:08:08.000 2001-01-01 09:09:09.000
汽车B 2001-01-01 03:03:03.000 2001-01-01 04:04:04.000
汽车C 2001-01-01 05:05:05.000 2001-01-01 06:06:06.000
汽车D 2001-01-01 07:07:07.000 NULL

[xuezhanliang6886]

up

[yucong]

[Quote=引用 20 楼 josy 的回复:]
加了一行测试数据，再改了一下，试试--


SQL code---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([名称] varchar(5),[时间] datetime,[状态] varchar(4))
insert [tb]
select '汽车A','2001-1-1 2:2:2','返回' union all
select '汽车B','2001-1-1 3:3:3','出发' union all
select '汽车C','2001-1-1 5:5:5','出发' union all
select '汽车B','2001-1-1 4:4:4','返回' uni…
[/Quote]



感觉这个回答是最贴边的，但还是有漏洞，请帮忙

[yucong]

例如以下数据
汽车A 出发 2009-4-2 下午 04:25:51
汽车B 返回 2009-4-2 下午 04:25:48
汽车B 出发 2009-4-2 下午 04:25:53
汽车A 返回 2009-4-2 下午 04:26:02
汽车A 出发 2009-4-2 下午 04:26:50
汽车A 返回 2009-4-2 下午 04:27:01
汽车C 返回 2009-4-2 下午 04:30:16
汽车C 出发 2009-4-2 下午 04:30:27
汽车A 出发 2009-4-2 下午 04:37:05
汽车A 返回 2009-4-2 下午 04:37:16
汽车C 返回 2009-4-2 下午 04:40:39
汽车C 出发 2009-4-2 下午 04:40:44
汽车B 返回 2009-4-2 下午 04:43:02
汽车B 出发 2009-4-2 下午 04:43:04
汽车A 出发 2009-4-2 下午 04:49:55
汽车A 返回 2009-4-2 下午 04:50:06
汽车C 返回 2009-4-2 下午 04:50:57
汽车C 出发 2009-4-2 下午 04:51:02
汽车B 返回 2009-4-2 下午 04:54:41
汽车B 出发 2009-4-2 下午 04:54:45
汽车A 出发 2009-4-2 下午 04:58:30
汽车A 返回 2009-4-2 下午 04:58:41
汽车C 返回 2009-4-2 下午 05:01:19
汽车C 出发 2009-4-2 下午 05:01:30
汽车A 出发 2009-4-2 下午 05:08:43
汽车A 返回 2009-4-2 下午 05:08:54
汽车C 返回 2009-4-2 下午 05:11:37
汽车C 出发 2009-4-2 下午 05:11:48
汽车A 出发 2009-4-2 下午 05:12:01
汽车A 返回 2009-4-2 下午 05:12:12
汽车A 出发 2009-4-2 下午 05:14:07
汽车A 返回 2009-4-2 下午 05:14:18
汽车A 出发 2009-4-2 下午 05:17:34
汽车A 返回 2009-4-2 下午 05:17:45
汽车C 返回 2009-4-2 下午 05:21:56
汽车C 出发 2009-4-2 下午 05:22:01
汽车A 出发 2009-4-2 下午 05:29:40
汽车A 返回 2009-4-2 下午 05:29:51
汽车C 返回 2009-4-2 下午 05:32:19
汽车C 出发 2009-4-2 下午 05:32:30
汽车A 出发 2009-4-2 下午 05:41:51
汽车A 返回 2009-4-2 下午 05:42:02
汽车C 返回 2009-4-2 下午 05:42:37
汽车C 出发 2009-4-2 下午 05:42:48
汽车A 出发 2009-4-2 下午 05:46:05
汽车A 返回 2009-4-2 下午 05:46:16
汽车C 返回 2009-4-2 下午 05:52:59
汽车C 出发 2009-4-2 下午 05:53:04
汽车A 出发 2009-4-2 下午 05:53:44
汽车A 返回 2009-4-2 下午 05:53:55
汽车A 出发 2009-4-2 下午 05:54:40
汽车A 返回 2009-4-2 下午 05:54:51
汽车A 出发 2009-4-2 下午 06:00:33
汽车A 返回 2009-4-2 下午 06:00:44
汽车C 返回 2009-4-2 下午 06:03:16
汽车C 出发 2009-4-2 下午 06:03:27
汽车A 出发 2009-4-2 下午 06:06:27
汽车A 返回 2009-4-2 下午 06:06:38
汽车A 出发 2009-4-3 上午 08:44:46
汽车A 返回 2009-4-3 上午 08:44:57
汽车C 返回 2009-4-3 上午 08:45:16
汽车C 出发 2009-4-3 上午 08:45:27
汽车A 出发 2009-4-3 上午 08:50:06
汽车A 返回 2009-4-3 上午 08:50:17
汽车C 返回 2009-4-3 上午 08:55:40
汽车C 出发 2009-4-3 上午 08:55:45
汽车A 出发 2009-4-3 上午 09:04:45
汽车A 返回 2009-4-3 上午 09:04:56
汽车C 返回 2009-4-3 上午 09:05:58
汽车C 出发 2009-4-3 上午 09:06:03
汽车B 返回 2009-4-3 上午 09:06:42
汽车B 出发 2009-4-3 上午 09:06:46
汽车B 返回 2009-4-3 上午 09:09:33
汽车A 出发 2009-4-3 上午 09:09:37
汽车B 出发 2009-4-3 上午 09:09:36
汽车A 返回 2009-4-3 上午 09:09:48
汽车A 出发 2009-4-3 上午 09:14:13
汽车A 返回 2009-4-3 上午 09:14:31
汽车C 返回 2009-4-3 上午 09:16:20
汽车C 出发 2009-4-3 上午 09:16:31
汽车A 出发 2009-4-3 上午 09:20:06
汽车A 返回 2009-4-3 上午 09:20:17
汽车C 返回 2009-4-3 上午 09:26:38
汽车C 出发 2009-4-3 上午 09:26:49
汽车A 出发 2009-4-3 上午 09:32:46
汽车A 返回 2009-4-3 上午 09:32:57
汽车C 返回 2009-4-3 上午 09:36:56
汽车C 出发 2009-4-3 上午 09:37:07
汽车A 出发 2009-4-3 上午 09:39:59
汽车A 返回 2009-4-3 上午 09:40:10
汽车B 返回 2009-4-3 上午 09:46:40
汽车B 出发 2009-4-3 上午 09:46:44
汽车C 返回 2009-4-3 上午 09:47:20
汽车C 出发 2009-4-3 上午 09:47:25
汽车C 返回 2009-4-3 上午 09:57:38
汽车C 出发 2009-4-3 上午 09:57:49
汽车B 返回 2009-4-3 上午 09:59:43
汽车B 出发 2009-4-3 上午 09:59:44
汽车A 出发 2009-4-3 上午 10:05:11
汽车A 返回 2009-4-3 上午 10:05:22
汽车C 出发 2009-4-3 上午 10:05:46
汽车A 出发 2009-4-3 上午 10:06:21
汽车A 返回 2009-4-3 上午 10:06:32
汽车C 返回 2009-4-3 上午 10:07:58
汽车C 出发 2009-4-3 上午 10:08:09
汽车A 出发 2009-4-3 上午 10:08:25
汽车A 返回 2009-4-3 上午 10:08:36
汽车A 出发 2009-4-3 上午 10:09:39
汽车A 返回 2009-4-3 上午 10:09:50
汽车A 出发 2009-4-3 上午 10:13:43
汽车A 返回 2009-4-3 上午 10:14:04
汽车C 返回 2009-4-3 上午 10:17:26
汽车A 出发 2009-4-3 上午 10:17:59
汽车A 返回 2009-4-3 上午 10:18:10
汽车B 返回 2009-4-3 上午 10:18:09
汽车B 出发 2009-4-3 上午 10:18:20
汽车C 返回 2009-4-3 上午 10:18:29
汽车C 出发 2009-4-3 上午 10:18:40
汽车C 出发 2009-4-3 上午 10:19:07
汽车D 返回 2009-4-3 上午 10:24:25
汽车D 出发 2009-4-3 上午 10:24:42
汽车B 出发 2009-4-3 上午 10:24:42
汽车C 出发 2009-4-3 上午 10:24:57
汽车D 返回 2009-4-3 上午 10:24:58
汽车B 返回 2009-4-3 上午 10:31:29
汽车B 出发 2009-4-3 上午 10:32:52
汽车C 返回 2009-4-3 上午 10:33:01
汽车C 出发 2009-4-3 上午 10:33:06
汽车B 返回 2009-4-3 上午 10:33:25
汽车A 出发 2009-4-3 上午 11:46:58
汽车A 返回 2009-4-3 上午 11:47:09
汽车C 返回 2009-4-3 上午 11:48:44
汽车C 出发 2009-4-3 上午 11:48:55
汽车A 出发 2009-4-8 下午 01:11:53
汽车A 返回 2009-4-8 下午 01:12:04
汽车A 出发 2009-4-9 上午 09:28:22
汽车A 返回 2009-4-9 上午 09:28:33