现有一张数据表
Name Time Type
AAA 2009-12-25 1
BBB 2009-12-25 1
BBB 2009-12-26 3
CCC 2009-12-25 1
CCC 2009-12-24 2
CCC 2009-12-26 5
需要的结果是(重名的只要保留type字段值最大的那条记录)Name Time Type
AAA 2009-12-25 1
BBB 2009-12-26 3
CCC 2009-12-26 5
Name Time Type
AAA 2009-12-25 1
BBB 2009-12-25 1
BBB 2009-12-26 3
CCC 2009-12-25 1
CCC 2009-12-24 2
CCC 2009-12-26 5
需要的结果是(重名的只要保留type字段值最大的那条记录)Name Time Type
AAA 2009-12-25 1
BBB 2009-12-26 3
CCC 2009-12-26 5
from tab a,(select name,max(type) as type from tab group by name) b
where a.Name=b.Name and A.Type=b.Type
create table tb(Name varchar(10), Time datetime, Type int)
insert into tb values('AAA' , '2009-12-25' , 1 )
insert into tb values('BBB' , '2009-12-25' , 1 )
insert into tb values('BBB' , '2009-12-26' , 3 )
insert into tb values('CCC' , '2009-12-25' , 1 )
insert into tb values('CCC' , '2009-12-24' , 2 )
insert into tb values('CCC' , '2009-12-26' , 5 )
goselect t.* from tb t where type = (select max(type) from tb where Name = t.Name) order by t.Nameselect t.* from tb t where not exists (select 1 from tb where Name = t.Name and type > t.type) order by t.Namedrop table tb/*
Name Time Type
---------- ------------------------------------------------------ -----------
AAA 2009-12-25 00:00:00.000 1
BBB 2009-12-26 00:00:00.000 3
CCC 2009-12-26 00:00:00.000 5(所影响的行数为 3 行)Name Time Type
---------- ------------------------------------------------------ -----------
AAA 2009-12-25 00:00:00.000 1
BBB 2009-12-26 00:00:00.000 3
CCC 2009-12-26 00:00:00.000 5(所影响的行数为 3 行)*/
from 一张数据表 a
where not exists (
select 1 from 一张数据表
where Name = a.Name
and type > a.type)
select 'AAA' name,to_date('2009-12-25','yyyy-mm-dd') time, 1 type from dual
union all
select 'BBB' name,to_date('2009-12-25','yyyy-mm-dd') time, 1 type from dual
union all
select 'BBB' name,to_date('2009-12-26','yyyy-mm-dd') time, 3 type from dual
union all
select 'CCC' name,to_date('2009-12-25','yyyy-mm-dd') time, 1 type from dual
union all
select 'CCC' name,to_date('2009-12-24','yyyy-mm-dd') time, 2 type from dual
union all
select 'CCC' name,to_date('2009-12-26','yyyy-mm-dd') time, 5 type from dual
)
select * from(
select name,time,type,row_number()over(partition by name order by type desc) rn from temp
) where rn = 1
max(time)keep(dense_rank last order by type),
max(type)
from table1
group by name如果type不会重复的话,也可以用
not exists或row_number()来处理
Name Time Type
AAA 2009-12-25 1
AAA 2009-12-25 1
BBB 2009-12-25 1
BBB 2009-12-26 3
CCC 2009-12-25 1
CCC 2009-12-24 2
CCC 2009-12-26 5
from (
select t.*,row_number() over(partition by name order by type desc) RN
from table_a t
) a
where a.RN=1