分组排名row_number()

create table t1

(

    id int not null identity(1,1),

    class varchar(3),

    project varchar(5),

    model int,

    spe int,

    Rd_DwgNO varchar(4)

)

insert into t1

select 'DNV', 'H1116', 468, 12000, 'NX03' union all

select 'DNV', 'H1116', 468, 12000, 'NX03' union all

select 'DNV', 'H1116', 468, 12000, 'NX03' union all

select 'DNV', 'H1116', 468, 12001, 'NX04' union all

select 'DNV', 'H1116', 468, 12001, 'NX04' union all

select 'DNV', 'H1116', 468, 12002, 'NX05' union all

select 'DNV', 'H1116', 468, 12002, 'NX05'

select * from t1

 

select * from

(select ROW_NUMBER() over(partition by spe order by id) as rowindex,* from t1) as a

where rowindex=1

 

-------------------------

rowindex    id    class    project    model    spe    Rd_DwgNO

1    1    DNV    H1116    468    12000    NX03

1    4    DNV    H1116    468    12001    NX04

1    6    DNV    H1116    468    12002    NX05

转载于:https://www.cnblogs.com/windback/archive/2012/03/13/2393873.html