谁会sql 语句?
a字段 b字段
ZZ:BW:6000 2.00000000
ZZ:BW:6000 5.00000000
ZZ:BW:6000 45.00000000
ZZ:BW:6000 78.00000000
ZZ:BW:6000 87.00000000
ZZ:BW:6001 2.00000000我要达到的效果是:
ZZ:BW:6000 2.00000000 1
ZZ:BW:6000 5.00000000 2
ZZ:BW:6000 45.00000000 3
ZZ:BW:6000 78.00000000 4
ZZ:BW:6000 87.00000000 5
ZZ:BW:6001 2.00000000 1
也就是相同的编个流水号
怎样写sql语句?
a字段 b字段
ZZ:BW:6000 2.00000000
ZZ:BW:6000 5.00000000
ZZ:BW:6000 45.00000000
ZZ:BW:6000 78.00000000
ZZ:BW:6000 87.00000000
ZZ:BW:6001 2.00000000我要达到的效果是:
ZZ:BW:6000 2.00000000 1
ZZ:BW:6000 5.00000000 2
ZZ:BW:6000 45.00000000 3
ZZ:BW:6000 78.00000000 4
ZZ:BW:6000 87.00000000 5
ZZ:BW:6001 2.00000000 1
也就是相同的编个流水号
怎样写sql语句?
(
GroupId int identity(1,1) primary key,
GroupA varchar(20),
GroupB varchar(20)
)insert into joly values('a1','b1')
insert into joly values('a1','b2')
insert into joly values('a2','b1')
insert into joly values('a2','b2')
insert into joly values('a2','b3')select * from joly
select *,(select count(*)+1 from joly where joly.groupa=t.groupa and t.groupb>joly.groupb ) as 排名 from joly as t order by groupa,groupb
select *,row=(select count(1) from table1 where a字段=t.a字段 and b字段<=t.b字段 )
from
Table1 tSQL05:
select *,row=row_number()over(partition by a字段 order by b字段) from T
insert @t select
'ZZ:BW:6000', '2.00000000' union all select
'ZZ:BW:6000', '5.00000000' union all select
'ZZ:BW:6000', '45.00000000' union all select
'ZZ:BW:6000', '78.00000000' union all select
'ZZ:BW:6000', '87.00000000' union all select
'ZZ:BW:6001', '2.00000000'
select * ,(select count(*)+1 from @t t1 where t1.a=t2.a and t1.b<t2.b )as 排名 from @t t2 order by a,b(6 行受影响)
a b 排名
------------------ ------------------ -----------
ZZ:BW:6000 2.00000000 1
ZZ:BW:6000 45.00000000 2
ZZ:BW:6000 5.00000000 3
ZZ:BW:6000 78.00000000 4
ZZ:BW:6000 87.00000000 5
ZZ:BW:6001 2.00000000 1
insert @t select
'ZZ:BW:6000', '2.00000000' union all select
'ZZ:BW:6000', '5.00000000' union all select
'ZZ:BW:6000', '45.00000000' union all select
'ZZ:BW:6000', '78.00000000' union all select
'ZZ:BW:6000', '87.00000000' union all select
'ZZ:BW:6001', '2.00000000'
select * ,(select count(*)+1 from @t t1 where t1.a=t2.a and t1.b<t2.b )as 排名 from @t t2 order by a,b(6 行受影响)
a b 排名
------------------ --------------------------------------- -----------
ZZ:BW:6000 2.00000000 1
ZZ:BW:6000 5.00000000 2
ZZ:BW:6000 45.00000000 3
ZZ:BW:6000 78.00000000 4
ZZ:BW:6000 87.00000000 5
ZZ:BW:6001 2.00000000 1