如何将最高金额排名为最低金额 [英] How to rank highest amount to lowest amount

问题描述

我有这张桌子



ID NAME AMOUNT

1 AAA 50000

2 BBB 49999

3 CCC 15001

4 DDD 28000

5 KKK 15000

6 EEE 28005

7 SSS 50000



i需要排名如此50000 1st，49999 2nd，3rd 35000 ..同样增加的金额将获得最高排名。



我尝试了什么：



i尝试过rank，dense_rank，ntile，row count

i have this table

ID NAME AMOUNT
1 AAA 50000
2 BBB 49999
3 CCC 15001
4 DDD 28000
5 KKK 15000
6 EEE 28005
7 SSS 50000

i need ranking like this 50000 1st, 49999 2nd , 3rd 35000.. likewise increased amount will get highest rank .

What I have tried:

i have tried rank,dense_rank,ntile,row count

推荐答案

尝试：

Try:

SELECT * FROM MyTable ORDER BY Amount DESC

如果您要存储金额在VARCHAR或NVARCHAR列中 - 在这种情况下，比较将基于字符串而不是数字，整个比较将基于第一个不同的角色。所以你的排序顺序将变为



The only reason that might not give you what you want is if you are storing Amount in a VARCHAR or NVARCHAR column - in which case the comparison will be string based instead of numeric, and the whole comparison will be based on teh first different character. So your sort order would become

1
10
11
12
...
18
19
2
20
21
...

如果是，请将数据库更改为使用数字列。< br $> b $ b





你的意思是：

If you are, then change your DB to use a numeric column instead.


[edit]
Do you mean:

SELECT ID, Name, Amount,  RANK () OVER (ORDER BY Amount DESC)  as Rank
      FROM MyTable
ORDER BY Amount DESC

哪个会给你：

Which will give you:

ID	Name	    Amount	Rank
1	AAA       	5000	1
4	SSS       	5000	1
2	BBB       	4999	3
3	CCC       	1500	4

或：

Or:

SELECT ID, Name, a.Amount, b.Rank
FROM MyTable a
JOIN (SELECT Amount, RANK () OVER (ORDER BY Amount DESC)  as Rank
      FROM (SELECT DISTINCT Amount FROM MyTable)x) b ON a.Amount = b.Amount
ORDER BY a.Amount DESC

哪个会给你：

Which will give you:

ID	Name	    Amount	Rank
1	AAA       	5000	1
4	SSS       	5000	1
2	BBB       	4999	2
3	CCC       	1500	3

[/ edit]

[/edit]

如果你想添加排名，那么首先你需要选择金额列中的 DISTINCT 记录



然后，从那个 NESTED SELECT 你需要得到 ROW_NUMBER 当它按金额订购时，降序



然后用在查询中，您可以在原始 SELECT 上执行 JOIN ，使用金额作为标准



祝您好运

If you want to add a rank, then first you need to select the DISTINCT records from the amount column

Then, from that NESTED SELECT you need to get the ROW_NUMBER when it's ordered by the amount, descending

Then with THAT query, you can do a JOIN on your original SELECT, using amount as the criteria

Best of luck

SELECT *，DENSE_RANK（）OVER（ORDER BY金额DESC）

来自数据

SELECT *, DENSE_RANK() OVER(ORDER BY amount DESC)
FROM Data

1	AAA	50000	1
7	SSS	50000	1
2	BBB	49999	2
6	EEE	28005	3
4	DDD	28000	4
3	CCC	15001	5
5	KKK	15000	6

$ b $bhoặc



SELECT *，RANK（）OVER（ORDER BY金额DESC）

FROM数据

hoặc

SELECT *, RANK() OVER(ORDER BY amount DESC)
FROM Data

1	AAA	50000	1
7	SSS	50000	1
2	BBB	49999	3
6	EEE	28005	4
4	DDD	28000	5
3	CCC	15001	6
5	KKK	15000	7

这篇关于如何将最高金额排名为最低金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！