按条件获取不重复数据

[dropping]

表T中现有数据如下图：

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

[问苍天人生几何]

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
*/

[人生无悔]

--你的记录好像有错噢
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 行受影响)

*/

[dropping]

第2幅图片有问题，你们的结果都是对的。

[wobuainiyes]

借用1#的测试数据。
子查询中根据取数据的规则为每一行产生序号。
再利用INNER JOIN取出序号为1的记录，则为结果。

SELECT t1.*
FROM @T AS t1
	INNER JOIN (
		SELECT id
				,ROW_NUMBER() OVER ( PARTITION BY NodeName ORDER BY Flag DESC,ID DESC ) AS RowNumber
		FROM @T ) AS t2
		ON t1.ID = t2.ID AND t2.RowNumber = 1
/*
ID          NodeName ArrvalTime              Flag
----------- -------- ----------------------- -----------
2           上海       2012-08-24 23:15:20.877 0
3           昆山       2012-08-24 23:15:20.877 0
4           苏州       2012-08-24 23:15:20.877 0
6           无锡       2012-08-24 23:15:20.877 1
7           常州       2012-08-24 23:15:20.877 1
9           镇江       2012-08-24 23:15:20.877 0
*/