需要使用：分区函数用法(partition by 字段)
select *,row_number() over(partition by item order by date ) as index from tab
分区索引
-------------------------------------------
SQL Server
select * from (select * , row_number() over(partition by id order by state desc) as sequence
from mian ) a where sequence =1
--where 前要有个表名
oracle中要如下修改：
select * from (select t.* , row_number() over(partition by t.id order by t.state desc) as sequence
from mian t ) a where sequence =1
oracle 中要加个别名t，与sqlserver2005中不同。