SQL高手帮忙看下！

Question

表中有如下5列
ID     carID     AreaID    ArriveTime   LeftTime
1       1         1         2012-3-1     2012-3-2
2       1         2         2012-3-2     2012-3-3
3       1         1         2012-3-3     2012-3-4
4       1         2         2012-3-4     2012-3-5
5       1         1         2012-3-1     2012-3-2
6       2         1         2012-3-1     2012-3-2
7       2         2         2012-3-2     2012-3-3
8       2         1         2012-3-4     2012-3-5ID为主键自动增长，怎么写SQL使CarID相等的情况下，两条数据合并
例如 ID 为1，2两条数据合并成一个新的表，表中有如下字段：
NewID  CarID    AreaID1    AreaID2    time1   time2
这里time1为ID=1的  LeftTime，time2为ID=2 的 ArriveTime，
以此类推，ID为2,3合并，3,4合并，前提是CarID相等，当ID=5，表中后面的数据没有CarID=1的数据的时候，
AreaID2，time2字段为空求高手指教！

Answer 1

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[carID] int,[AreaID] int,[ArriveTime] datetime,[LeftTime] datetime)
insert [tb]
select 1,1,1,'2012-3-1','2012-3-2' union all
select 2,1,2,'2012-3-2','2012-3-3' union all
select 3,1,1,'2012-3-3','2012-3-4' union all
select 4,1,2,'2012-3-4','2012-3-5' union all
select 5,1,1,'2012-3-1','2012-3-2' union all
select 6,2,1,'2012-3-1','2012-3-2' union all
select 7,2,2,'2012-3-2','2012-3-3' union all
select 8,2,1,'2012-3-4','2012-3-5'
--------------开始查询--------------------------
;with t as
(
select *,new_id=row_number() over( order by id) from tb
)
select a.id,a.carID,a.AreaID,a.ArriveTime,a.LeftTime,b.ArriveTime,b.LeftTime 
from t a left join t b on a.new_id=b.new_id-1 and a.[carID]=b.[carID]
where a.new_id%2=1 or  b.id is null
----------------结果----------------------------
/* 
id          carID       AreaID      ArriveTime              LeftTime                ArriveTime              LeftTime
----------- ----------- ----------- ----------------------- ----------------------- ----------------------- -----------------------
1           1           1           2012-03-01 00:00:00.000 2012-03-02 00:00:00.000 2012-03-02 00:00:00.000 2012-03-03 00:00:00.000
3           1           1           2012-03-03 00:00:00.000 2012-03-04 00:00:00.000 2012-03-04 00:00:00.000 2012-03-05 00:00:00.000
5           1           1           2012-03-01 00:00:00.000 2012-03-02 00:00:00.000 NULL                    NULL
7           2           2           2012-03-02 00:00:00.000 2012-03-03 00:00:00.000 2012-03-04 00:00:00.000 2012-03-05 00:00:00.000
8           2           1           2012-03-04 00:00:00.000 2012-03-05 00:00:00.000 NULL                    NULL(5 行受影响)*/

Answer 2

需要根据id 重新生成连续的id在进行这样的对比，
原来的id 是无法保证连续的，如果是2000需要弄个临时表。

Answer 3

NewID CarID AreaID1  time1     AreaID2   time2
1       1      1     2012-3-2    2      2012-3-2
1       1      2     2012-3-3    1      2012-3-3
1       1      1     2012-3-4    2      2012-3-4
1       1      2     2012-3-5    1      2012-3-1
1       1      1     2012-3-2    null    null
1       2      1     2012-3-2    2      2012-3-2
1       2      2     2012-3-3    1      2012-3-4
1       2      1     2012-3-5    null     nullAreaID1  time1    两字段取的是第一条数据中的AreaID,LeftTime
AreaID2  time2    两字段取的是第二条数据中的AreaID,ArriveTime
如果没有找到下一条数据  AreaID2  time2 为 null

Answer 4

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[carID] int,[AreaID] int,[ArriveTime] datetime,[LeftTime] datetime)
insert [tb]
select 1,1,1,'2012-3-1','2012-3-2' union all
select 2,1,2,'2012-3-2','2012-3-3' union all
select 3,1,1,'2012-3-3','2012-3-4' union all
select 4,1,2,'2012-3-4','2012-3-5' union all
select 5,1,1,'2012-3-1','2012-3-2' union all
select 6,2,1,'2012-3-1','2012-3-2' union all
select 7,2,2,'2012-3-2','2012-3-3' union all
select 8,2,1,'2012-3-4','2012-3-5'
--------------开始查询--------------------------
;with t as
(
select *,new_id=row_number() over( order by id) from tb
)
select a.id,a.carID,a.AreaID,a.LeftTime,b.AreaID,b.LeftTime 
from t a left join t b on a.new_id=b.new_id-1 and a.[carID]=b.[carID]
----------------结果----------------------------
/* 
id          carID       AreaID      LeftTime                AreaID      LeftTime
----------- ----------- ----------- ----------------------- ----------- -----------------------
1           1           1           2012-03-02 00:00:00.000 2           2012-03-03 00:00:00.000
2           1           2           2012-03-03 00:00:00.000 1           2012-03-04 00:00:00.000
3           1           1           2012-03-04 00:00:00.000 2           2012-03-05 00:00:00.000
4           1           2           2012-03-05 00:00:00.000 1           2012-03-02 00:00:00.000
5           1           1           2012-03-02 00:00:00.000 NULL        NULL
6           2           1           2012-03-02 00:00:00.000 2           2012-03-03 00:00:00.000
7           2           2           2012-03-03 00:00:00.000 1           2012-03-05 00:00:00.000
8           2           1           2012-03-05 00:00:00.000 NULL        NULL(8 行受影响)
*/我写的 你都不看看 自己改一下

Answer 5

NewID CarID AreaID1 time1 AreaID2 time2
1 1 1 2012-3-2 2 2012-3-2
2 1 2 2012-3-3 1 2012-3-3
3 1 1 2012-3-4 2 2012-3-4
4 1 2 2012-3-5 1 2012-3-1
5 1 1 2012-3-2 null null
6 2 1 2012-3-2 2 2012-3-2
7 2 2 2012-3-3 1 2012-3-4
8 2 1 2012-3-5 null null--红色部份应该为null？

Answer 6

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[carID] int,[AreaID] int,[ArriveTime] Datetime,[LeftTime] Datetime)
Insert #T
select 1,1,1,'2012-3-1','2012-3-2' union all
select 2,1,2,'2012-3-2','2012-3-3' union all
select 3,1,1,'2012-3-3','2012-3-4' union all
select 4,1,2,'2012-3-4','2012-3-5' union all
select 5,1,1,'2012-3-1','2012-3-2' union all
select 6,2,1,'2012-3-1','2012-3-2' union all
select 7,2,2,'2012-3-2','2012-3-3' union all
select 8,2,1,'2012-3-4','2012-3-5'
Go
Select 
a.ID, a.CarID,a.[AreaID],time1=a.LeftTime,AreaID2=CASE WHEN b.[AreaID]<>a.[AreaID] THEN b.[AreaID] END,time2=CASE WHEN b.[AreaID]<>a.[AreaID] THEN b.[ArriveTime] END
from #T AS a
LEFT JOIN #T AS b ON a.ID=b.ID-1
/*
1 1 1 2012-03-02 00:00:00.000 2 2012-03-02 00:00:00.000
2 1 2 2012-03-03 00:00:00.000 1 2012-03-03 00:00:00.000
3 1 1 2012-03-04 00:00:00.000 2 2012-03-04 00:00:00.000
4 1 2 2012-03-05 00:00:00.000 1 2012-03-01 00:00:00.000
5 1 1 2012-03-02 00:00:00.000 NULL NULL
6 2 1 2012-03-02 00:00:00.000 2 2012-03-02 00:00:00.000
7 2 2 2012-03-03 00:00:00.000 1 2012-03-04 00:00:00.000
8 2 1 2012-03-05 00:00:00.000 NULL NULL
*/

Answer 7

加上a.CarID=b.CarID条件
--ID不连续有删除时这样用Select 
a.ID, a.CarID,a.[AreaID],time1=a.LeftTime,AreaID2=CASE WHEN b.[AreaID]<>a.[AreaID] THEN b.[AreaID] END,time2=CASE WHEN b.[AreaID]<>a.[AreaID] THEN b.[ArriveTime] END
from #T AS a
LEFT JOIN #T AS b ON a.CarID=b.CarID AND b.ID=(SELECT MIN(ID) FROM  #T  WHERE ID>a.ID)
--
Select 
a.ID, a.CarID,a.[AreaID],time1=a.LeftTime,AreaID2=CASE WHEN b.[AreaID]<>a.[AreaID] THEN b.[AreaID] END,time2=CASE WHEN b.[AreaID]<>a.[AreaID] THEN b.[ArriveTime] END
from #T AS a
LEFT JOIN #T AS b ON a.CarID=b.CarID AND a.ID=b.ID-1
/*
1 1 1 2012-03-02 00:00:00.000 2 2012-03-02 00:00:00.000
2 1 2 2012-03-03 00:00:00.000 1 2012-03-03 00:00:00.000
3 1 1 2012-03-04 00:00:00.000 2 2012-03-04 00:00:00.000
4 1 2 2012-03-05 00:00:00.000 1 2012-03-01 00:00:00.000
5 1 1 2012-03-02 00:00:00.000 NULL NULL
6 2 1 2012-03-02 00:00:00.000 2 2012-03-02 00:00:00.000
7 2 2 2012-03-03 00:00:00.000 1 2012-03-04 00:00:00.000
8 2 1 2012-03-05 00:00:00.000 NULL NULL
*/