ÇóÒ»¸öSQL£º·ÖÇøÅÅÃû - MS-SQL Server / »ù´¡Àà

¼ÙÈçÎÒÓÐÊý¾Ý
Reguon User_name Amount
¶«Çø ÕÅÈý 6834.4
¶«Çø ÕŹû 5000
¶«Çø ÕÅÂüÓñ 2000
±±Çø ÀîÓñ 1250
±±Çø ÀîËÄ 1505.9
±±Çø ÍõÎå 572.3
ÄÏÇø Ǯһ 3417.2
ÔõôÓÃSQLʵÏÖ·ÖÇøÅÅÐò
Reguon User_name Amount Sequence
¶«Çø ÕÅÈý 6834.4 1
¶«Çø ÕŹû 5000 2
¶«Çø ÕÅÂüÓñ 2000 3
±±Çø ÀîÓñ 12500 1
±±Çø ÀîËÄ 1505.9 2
±±Çø ÍõÎå 572.3 3
ÄÏÇø Ǯһ 3417.2 1

лл

---

select Reguon User_name Amount,Sequence=row_number(partition by Reguon order by Amount desc) from tb

---

SQL code:
select *,
Sequence=(select count(1)+1 from tb where Reguon=t.Reguon and Amount>t.Amount)
from tb t

---

SQL code:
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Reguon] varchar(4),[User_name] varchar(6),[Amount] numeric(5,1))
insert [tb]
select '¶«Çø','ÕÅÈý',6834.4 union all
select '¶«Çø','ÕŹû',5000 union all
select '¶«Çø','ÕÅÂüÓñ',2000 union all
select '±±Çø','ÀîÓñ',1250 union all
select '±±Çø','ÀîËÄ',1505.9 union all
select '±±Çø','ÍõÎå',572.3 union all
select 'ÄÏÇø','Ǯһ',3417.2

select *,
Sequence=(select count(1)+1 from tb where Reguon=t.Reguon and Amount>t.Amount)
from tb t

/**
Reguon User_name Amount