--> 测试数据：[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 行受影响)
*/

------解决方案--------------------


加上a.CarID=b.CarID条件
SQL code

--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)