declare @T table(ID int identity(1,1),NodeName nvarchar(2),ArrvalTime datetime,Flag int)
insert into @T
select N'上海',getdate(),0 union all
select N'上海',getdate(),0 union all
select N'昆山',getdate(),0 union all
select N'苏州',getdate(),0 union all
select N'无锡',getdate(),0 union all
select N'无锡',getdate(),1 union all
select N'常州',getdate(),1 union all
select N'常州',getdate(),0 union all
select N'镇江',getdate(),0


select * from @T where Flag=1
union all
select ID,NodeName,ArrvalTime,Flag from
(
    select row_number() over(partition by NodeName order by NodeName,ID desc) rn,* 
    from 
    (
        select * from @T a where not exists (select 1 from @T where a.NodeName=NodeName and Flag=1)
    ) t
) tt
where tt.rn=1
order by ID

/*
ID          NodeName ArrvalTime              Flag
----------- -------- ----------------------- -----------
2           上海       2012-08-24 23:01:06.930 0
3           昆山       2012-08-24 23:01:06.930 0
4           苏州       2012-08-24 23:01:06.930 0
6           无锡       2012-08-24 23:01:06.930 1
7           常州       2012-08-24 23:01:06.930 1
9           镇江       2012-08-24 23:01:06.930 0
*/

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

SQL code

--你的记录好像有错噢
with t(ID,NodeName,ArrvalTime,Flag) as(
select 1,'上海','2012-08-24 16:06:00',0
union all select 2,'上海','2012-08-24 16:06:00',0
union all select 3,'昆山','2012-08-24 16:11:36',0
union all select 4,'苏州','2012-08-24 16:14:00',0
union all select 5,'无锡','2012-08-24 16:22:00',0
union all select 6,'无锡','2012-08-24 16:26:00',1
union all select 7,'常州','2012-08-24 18:20:00',1
union all select 8,'常州','2012-08-24 18:22:00',0
union all select 9,'镇江','2012-08-24 16:45:00',0
)
select ID,NodeName,ArrvalTime,Flag from(
    select *,row_number() 
    over(partition by NodeName order by Flag desc,ID desc) rn from t) t1
where rn=1
order by ID;
/*
ID          NodeName ArrvalTime          Flag
----------- -------- ------------------- -----------
2           上海       2012-08-24 16:06:00 0
3           昆山       2012-08-24 16:11:36 0
4           苏州       2012-08-24 16:14:00 0
6           无锡       2012-08-24 16:26:00 1
7           常州       2012-08-24 18:20:00 1
9           镇江       2012-08-24 16:45:00 0

(6 行受影响)

*/