表 Table1
字段 zid zname prv 数据
zid zname prv
2007 末層 0.01
2007 末層 0.03
2008 卸殼 0.01
2009 脫臘 0.01
要筛选出的结果:
2007 末層 0.03
2008 卸殼 0.01
2009 脫臘 0.01
字段 zid zname prv 数据
zid zname prv
2007 末層 0.01
2007 末層 0.03
2008 卸殼 0.01
2009 脫臘 0.01
要筛选出的结果:
2007 末層 0.03
2008 卸殼 0.01
2009 脫臘 0.01
create table table1
(
zid int,
zname varchar(50),
prv decimal(18,2)
)insert into table1
select 2007,'末層',0.01 union all
select 2007,'末層',0.03 union all
select 2008,'卸殼',0.01 union all
select 2009,'脫臘',0.01select *
from table1 b
where not exists(select * from table1 where b.zname =zname and b.prv <prv )
select *
from table1 b
where not exists(select * from table1 where b.zid =zid and b.prv <prv )不说清楚 以为zname相同取最大呢
select zid,zname,prv from table1 as a where not exists(select 1 from table1 as b where a.zid=b.zid and a.prv<b.prv)
//try
select distinct(zid,zname,prv) from table1