我有这样的数据,
a ab
a bc
d db
d dc
d ad
e as
我想得出这样的结果,sql应该怎样写:、
a ab 1
a bc 2
d db 1
d dc 2
d ad 3
e as 1
a ab
a bc
d db
d dc
d ad
e as
我想得出这样的结果,sql应该怎样写:、
a ab 1
a bc 2
d db 1
d dc 2
d ad 3
e as 1
insert into tb values('a' , 'ab')
insert into tb values('a' , 'bc')
insert into tb values('d' , 'db')
insert into tb values('d' , 'dc')
insert into tb values('d' , 'ad' )
insert into tb values('e' , 'as' )
goselect t.* , px = (select count(1) from tb where col1 = t.col1 and col2 < t.col2) + 1 from tb t order by t.col1 , px
drop table tb /*
col1 col2 px
---------- ---------- -----------
a ab 1
a bc 2
d ad 1
d db 2
d dc 3
e as 1(所影响的行数为 6 行)
*/
go
create table tb(col1 varchar(10),col2 varchar(10))
insert tb select 'a' ,'ab'
insert tb select 'a' ,'bc'
insert tb select 'd' , 'db'
insert tb select 'd' , 'dc'
insert tb select 'd', 'ad'
insert tb select 'e', 'as'
select * ,cnt=(select count(*) from tb where col1=t.col1 and col2<=t.col2) from tb t
/*col1 col2 cnt
---------- ---------- -----------
a ab 1
a bc 2
d db 2
d dc 3
d ad 1
e as 1
*/
col1 col2
a ab
a bc
d db
d dc
d ad
e as select *, Row_number()over(partition by col1 order by col1) as cnt
from table