SQL高手帮忙看下！解决办法

SQL高手帮忙看下！
表中有如下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-5

ID为主键自动增长，怎么写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字段为空

求高手指教！

[解决办法]

SQL code

--> 测试数据：[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 allselect 2,1,2,'2012-3-2','2012-3-3' union allselect 3,1,1,'2012-3-3','2012-3-4' union allselect 4,1,2,'2012-3-4','2012-3-5' union allselect 5,1,1,'2012-3-1','2012-3-2' union allselect 6,2,1,'2012-3-1','2012-3-2' union allselect 7,2,2,'2012-3-2','2012-3-3' union allselect 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.0002           1           2           2012-03-03 00:00:00.000 1           2012-03-04 00:00:00.0003           1           1           2012-03-04 00:00:00.000 2           2012-03-05 00:00:00.0004           1           2           2012-03-05 00:00:00.000 1           2012-03-02 00:00:00.0005           1           1           2012-03-02 00:00:00.000 NULL        NULL6           2           1           2012-03-02 00:00:00.000 2           2012-03-03 00:00:00.0007           2           2           2012-03-03 00:00:00.000 1           2012-03-05 00:00:00.0008           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] ENDfrom #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)