查询有重复的数据（没有重复的也显示）只显示一条，字段NAME，CITY，都相同的取icount最小的

[hainan]

表字段 ID，name， city， icount

查询有重复的数据（没有重复的也显示）只显示一条，字段NAME，CITY，都相同的取icount最小的
可以是SQL2005
谢谢

[华夏小卒]

select * from tb t
where (select count(*) from tb where NAME=t.NAME and CITYCITY=t.CITYCITY and t.icount<icount)<1

[bancxc]

select * from tb t
where not exists (
select 1 from tb where t.name=name and t.city=city and t.icount>icount
)

[xman_78tom]

select id,name,city,icount from (
select *, row_number() over (partition by name,city order by icount) rn from tab) t
where rn=1

[心中的彩虹]

[Quote=引用楼主 hainan 的回复:]
表字段 ID，name， city， icount

查询有重复的数据（没有重复的也显示）只显示一条，字段NAME，CITY，都相同的取icount最小的
可以是SQL2005
谢谢
[/Quote]

select ID,name,city,icount from tb a where not exists(select 1 from tb where a.name=name and a.city=city a.icount>icount)


--2 row_number() over是2005 新增的分析函数 很好
select ID,name,city,icount
from
(select ID,name,city,icount,
row_number() over(partition by name,city order by icount) rk
from tb) a
where a.rn=1

[水族杰纶]

--进来了就参与一下
select *
from tb t
where icount=(select min(icount)
              from tb
              where NAME=t.NAME and city=t.city)

[rart2008]

[Quote=引用 5 楼 wufeng4552 的回复:]

SQL code
--进来了就参与一下
select *
from tb t
where icount=(select min(icount)
from tb
where NAME=t.NAME and city=t.city)
[/Quote]
5楼正解！

[永生天地]

[Quote=引用 3 楼 xman_78tom 的回复:]
SQL code

select id,name,city,icount from (
select *, row_number() over (partition by name,city order by icount) rn from tab) t
where rn=1
[/Quote]
难道这样的速度最快