[SQL] How to get rank using ANSI SQL

Posted on | Edited on | In |

要對分數做排名，最直接的想法就對它們做ORDER BY。但是當分數有重複時候排名是要一樣，這時候ORDER BY就發揮不了作用。

不同的廠商的資料庫有提供不同的函數可以解決這個問題，假如沒有使用函數該如何做到呢?

建立資料表：

1
2
3
4
5
6
CREATE TABLE `golf` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

新增資料：

1
2
3
4
5
6
7
INSERT INTO `golf`(`name`,`score`) VALUES (A,74);
INSERT INTO `golf`(`name`,`score`) VALUES (B,79);
INSERT INTO `golf`(`name`,`score`) VALUES (C,79);
INSERT INTO `golf`(`name`,`score`) VALUES (D,82);
INSERT INTO `golf`(`name`,`score`) VALUES (E,89);
INSERT INTO `golf`(`name`,`score`) VALUES (F,89);
INSERT INTO `golf`(`name`,`score`) VALUES (G,98);

查詢：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 
    tmp.name,
    tmp.score,
    (SELECT 
            COUNT(*) + 1
        FROM
            (SELECT 
                golf.score
            FROM
                golf
            GROUP BY golf.score) AS tmp1
        WHERE
            tmp1.score > tmp.score) AS rank
FROM
    golf AS tmp
ORDER BY tmp.score DESC;

結果：

1
2
3
4
5
6
7
8
name	score	rank
G		98		1
E		89		2
F		89		2
D		82		3
B		79		4
C		79		4
A		74		5

想法：

先從排名這個概念下手，假如有三個人的分數(這三人分數都不一樣)大於我的分數，那麼我就是排名第四。因為分數重複的排名是一樣的，需要先對分數做一次GROUP BY，再根據上述的概念去算出排名。