如何添加排名列？

Question

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

我的数据如下。

 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

请帮忙。谢谢！ :)

原文由 Lady A 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

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

原文由 Ankit 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

   select * into #P from (
   select      1  as [MEMBER ID]        , 2000.00 as  [LOAN AMOUNT]  union   all
   select     2                      , 1000.00  union all
   select     3                         , 4000.00   union all
   select     4                         , 1000.00
 )P

 select rank()over(  order by [LOAN AMOUNT] desc) as srno,[MEMBER ID],[LOAN AMOUNT] from #P

输出：

         srno    MEMBER ID   LOAN AMOUNT
           1          3     4000.00
           2          1     2000.00
           3          2     1000.00
           3          4     1000.00

原文由 Ramdeo angh 发布，翻译遵循 CC BY-SA 3.0 许可协议