select dense_rank() over(order by field1),* from t_table order by field1
如果你有排序的可以用order by和count来筛选。
select rank() over(order by field1),* from t_table order by field1
create table tb(id int,iorder int) insert into tb select 1,2 union all select 1,3 union all select 1,6 union all select 2,3 union all select 2,1 union all select 2,4 goselect * from tb t where (select count(*) from tb where id = t.id and iorder <= t.iorder) <= 2drop table tb/************id iorder ----------- ----------- 1 2 1 3 2 3 2 1(4 行受影响)
create table tb(id int,iorder int)
insert into tb
select 1,2 union all
select 1,3 union all
select 1,6 union all
select 2,3 union all
select 2,1 union all
select 2,4
goselect *
from tb t
where (select count(*) from tb where id = t.id and iorder <= t.iorder) <= 2drop table tb/************id iorder
----------- -----------
1 2
1 3
2 3
2 1(4 行受影响)