iT邦幫忙

0

MySQL 求名次的問題

sql

假設有一張表
有兩個欄位分別是 member_id (會員編號) 與 score (分數)
如何用一句 MYSQL 語法 求得某一會員(例如 member_id=123) 的名次?

PS:我只需要此會員的名次 不必知道其他會員的名次

6
一級屠豬士
iT邦高手 1 級 ‧ 2011-07-07 19:02:56
最佳解答

Oracle 有Rank()可以使用,所以作排名很方便.
MySQL 使用一些技巧也可以達到排名的功能.

建立測試用的table test0707

<pre class="c" name="code">CREATE TABLE test0707 (
member_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
score INT
);

插入測試資料.

INSERT INTO test0707(score) VALUES
(386),(497),(538),(998),(346),(123),(675),(665),(386),(448);

--------------------
簡易排序
SELECT score, member_id
FROM test0707
ORDER BY score DESC;


SELECT @rownum := @rownum+1 AS 'Rank', a.*
FROM (SELECT score, member_id
       FROM test0707 
       ORDER BY score DESC) a, (SELECT @rownum := 0) r;
+------+-------+-----------+
| Rank | score | member_id |
+------+-------+-----------+
|    1 |   998 |         4 |
|    2 |   675 |         7 |
|    3 |   665 |         8 |
|    4 |   538 |         3 |
|    5 |   497 |         2 |
|    6 |   448 |        10 |
|    7 |   386 |         9 |
|    8 |   386 |         1 |
|    9 |   346 |         5 |
|   10 |   123 |         6 |
+------+-------+-----------+

上面可以看到兩個分數為386的為7/8名.這種Rank的方法,在作一些資料分析時方便,但是拿來發獎金或是
考試分發就會被抗議了.

改用以下的方法:
SELECT a.rank AS 'Rank', a.score, a.member_id 
FROM (SELECT member_id, score, @prev := @curr, @curr := score, @rank := IF(@prev = @curr, @rank, @rank+1) AS rank
FROM test0707, (SELECT @curr := null, @prev := null, @rank := 0) s
ORDER BY score DESC) a;

+------+-------+-----------+
| Rank | score | member_id |
+------+-------+-----------+
|    1 |   998 |         4 |
|    2 |   675 |         7 |
|    3 |   665 |         8 |
|    4 |   538 |         3 |
|    5 |   497 |         2 |
|    6 |   448 |        10 |
|    7 |   386 |         9 |
|    7 |   386 |         1 |
|    8 |   346 |         5 |
|    9 |   123 |         6 |
+------+-------+-----------+

這樣排名的方式就是相同分數會是同樣名次.

回到樓主的問題,若是單獨只要算一個人的名次,也不必用全部的排名,用上面網友提出的方法更好.
但若是需要後續計算好幾個名次的, 可以試試這樣的方式.裡面的排名部份,會放到cache裡.
然後再視需要取出對應的資料.

假設要取 member_id 為8 的名次

SELECT member_id, b.Rank
FROM (SELECT a.rank AS 'Rank', a.score, a.member_id 
       FROM (SELECT member_id, score, @prev := @curr, @curr := score, @rank := IF(@prev = @curr, @rank, @rank+1) AS rank
             FROM test0707, (SELECT @curr := null, @prev := null, @rank := 0) s
             ORDER BY score DESC) a) b
WHERE member_id = 8;

+-----------+------+
| member_id | Rank |
+-----------+------+
|         8 |    3 |
+-----------+------+

to樓主:
語法本來就是靈活運用..(^_^)
你能配合你的實際需求優化他是對的.
因為我是以一個測試說明為主的思路來一路進行.
第三層的包的方式,那時候就直接在那個兩層外加一個很直觀的,
具體執行速度會不會變慢?? 這就需要你那邊有大量實際資料
來比較才會具體. (^_^)

4
wiseguy
iT邦超人 1 級 ‧ 2011-07-07 12:29:28

同分的會在同一名次,但下一名則從缺。
也就是如果有三個第 7 名的話,那第8,9名就從缺。

<pre class="c" name="code">select count(*)+1 from TABLE
where score 
(select score from TABLE where member_id=123)
看更多先前的回應...收起先前的回應...
Albert iT邦高手 1 級 ‧ 2011-07-07 13:01:02 檢舉

應該是 大於等於 有幾位

不是小於 [123] 這個人的分數

一針見血讚

mybeldandy iT邦研究生 5 級 ‧ 2011-07-07 15:38:33 檢舉

應該是 大於有幾位 再加1

Albert iT邦高手 1 級 ‧ 2011-07-07 17:17:43 檢舉

>> 應該是 大於有幾位 再加1 >> 名次屬上升型 (好聽名次)
>> 應該是 大於等於有幾位 不再加1 >> 名次屬下降型 (保守名次)

小明:我們班上 這次英文 考第一名
媽媽:你是最優哪一個 !!
小明:不是媽媽你誤會了 滿分 15個 ?

Albert iT邦高手 1 級 ‧ 2011-07-07 17:20:11 檢舉

媽媽說 哪你是第 15名
因為這次 學測滿及分 超過 50個
滿及分上不了 台大醫學院
50個滿級分
哪就是買級分從 50明開始
不然誤會大了

4
賽門
iT邦超人 1 級 ‧ 2011-07-07 17:41:17
<pre class="c" name="code">set @a := 0;
select id 
  from (select @a := @a+1 id, member_id from TABLE order by score desc) a
 where a.member_id = 123;

提供您參考!

我要發表回答

立即登入回答