按条件获取不反复数据

按条件获取不重复数据
表T中现有数据如下图：

筛选出不同城市名称的列表，筛选的算法是：有相同城市名称的记录，如果flag有等于1的就取flag等于1的记录，如果没有等于1的记录就取ID最大的记录。
刷选的结果如下图：


[解决办法]

SQL code

declare @T table(ID int identity(1,1),NodeName nvarchar(2),ArrvalTime datetime,Flag int)insert into @Tselect N'上海',getdate(),0 union allselect N'上海',getdate(),0 union allselect N'昆山',getdate(),0 union allselect N'苏州',getdate(),0 union allselect N'无锡',getdate(),0 union allselect N'无锡',getdate(),1 union allselect N'常州',getdate(),1 union allselect N'常州',getdate(),0 union allselect N'镇江',getdate(),0select * from @T where Flag=1union allselect 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) ttwhere tt.rn=1order by ID/*ID          NodeName ArrvalTime              Flag----------- -------- ----------------------- -----------2           上海       2012-08-24 23:01:06.930 03           昆山       2012-08-24 23:01:06.930 04           苏州       2012-08-24 23:01:06.930 06           无锡       2012-08-24 23:01:06.930 17           常州       2012-08-24 23:01:06.930 19           镇江       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',0union all select 2,'上海','2012-08-24 16:06:00',0union all select 3,'昆山','2012-08-24 16:11:36',0union all select 4,'苏州','2012-08-24 16:14:00',0union all select 5,'无锡','2012-08-24 16:22:00',0union all select 6,'无锡','2012-08-24 16:26:00',1union all select 7,'常州','2012-08-24 18:20:00',1union all select 8,'常州','2012-08-24 18:22:00',0union 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) t1where rn=1order by ID;/*ID          NodeName ArrvalTime          Flag----------- -------- ------------------- -----------2           上海       2012-08-24 16:06:00 03           昆山       2012-08-24 16:11:36 04           苏州       2012-08-24 16:14:00 06           无锡       2012-08-24 16:26:00 17           常州       2012-08-24 18:20:00 19           镇江       2012-08-24 16:45:00 0(6 行受影响)*/