如何添加等级列？

小编典典

如何添加等级列？

sql

我想选择记录并确定每个相似数据的排名。

我的数据如下。

MEMBER ID | LOAN AMOUNT 
1         | 2,000.00 
2         | 1,000.00 
3         | 4,000.00
4         | 1,000.00

我想要的结果如下所示。

RANK|MEMBER ID|LOAN AMOUNT 
1   |3        |4,000.00
2   |1        |2,000.00
3   |2        |1,000.00
3   |4        |1,000.00

RANK是新列。我正在使用MS SQL Server 2008，并创建了一个如下所示的视图表，但它没有达到所需的结果。

  select rank=count(*), s1.MemberID, s1.Loan_Amount 
   from (select MemberID, Loan_Amount from vwPrintTop20Borrowers) s1
   group by s1.MemberID, s1.LOAN_AMOUNT
     order by rank, s1.Loan_amount DESC

请帮忙。谢谢！:)

阅读 231

2021-05-23

共1个答案

小编典典

SELECT ROW_NUMBER() 
        OVER (ORDER BY Loan_Amount DESC) AS Rank, 
    MemberID, LOAN_AMOUNT,
FROM vwPrintTop20Borrowers

2021-05-23