用SQL求得資料順位

原本是想要看看PHP Plurk API，在亂點roga部落格的MySQL分類時看到了roga大在去年六月有在想SQL的問題，於是心血來潮地試了一下。

（卻花了我兩個多小時，而這篇文不知道又要打多久了XD）

在仔細寫本文之前，先把結論呈現一下：DELETE FROM tbl
USING tbl INNER JOIN (
SELECT id, count(*) AS place
FROM (
SELECT t1.id
FROM tbl AS t1, tbl AS t2
WHERE t1.url_id = t2.url_id
AND t1.id <= t2.id
) AS t3
GROUP BY id
) AS t4
WHERE tbl.id = t4.id
AND t4.place > 1500其中tbl是roga大大的url_detail_history，單純是名字太長所以被我換掉。此法受限於資料庫系統對Multiple-table DELETE的支援度，已知MySQL支援。


以下詳述我的思考過程（有些是繞圈子，所以看起來可能跟結果沒啥關係）
roga原本的需求大致上是「資料表中url_id欄位相同的，只留下最新的1500筆」。而該資料表有一個`id`主鍵欄位是AUTO_INCREMENT。

我試著將問題從最簡單的模式開始推導：

第一步：求「指定url_id的最新資料」

這很直覺，使用Aggregate Function中的MAX()就可以了：SELECT MAX(id) FROM tbl WHERE url_id = 'XXXX'

第二步：求「各url_id最新的一筆」

也是基本題，加上GROUP BY就可以了：SELECT url_id, COUNT(*)
FROM tbl
GROUP BY url_id雖然沒什麼困難，不過我們稍比較一下這個和前一個SQL後會發現：

1. WHERE變成GROUP BY
2. SELECT區塊也要多一欄url_id，也就是GROUP BY用的欄位

第三步：求「各url_id最新的n筆」

咦....慘了，沒頭緒，也許我們第二步不應該這樣走。回到第一步，把問題改成「指定url_id的最新n筆」。（注意到題目的不同嗎？我一次只變動題目的一部分，嘗試在這樣的步驟中統整出比較有系統的解法。）

新的第二步：求「指定url_id的最新n筆」

嗯....如果不用MAX()的話，確實不難做到：SELECT id
FROM tbl
WHERE url_id = 'XXXX'
ORDER BY id DESC
LIMIT n

還是第三步：求「各url_id最新的n筆」

....我還是不知道要怎麼做

我卡了一個多小時，直到想起多年前在網路上看到的某個題目：「用SQL將名次排序出來」，雖然已經忘了網址在哪，但是解法卻非常的有趣。（效率如何我就不清楚了）：
假設資料表ss的資料如下：+---------+-------+
| student | score |
+---------+-------+
| 1 | 7 |
| 2 | 5 |
| 4 | 3 |
| 5 | 4 |
+---------+-------+而我們要產出的結果為：+---------+-------+-------+
| student | score | place |
+---------+-------+-------+
| 1 | 7 | 1 |
| 2 | 5 | 2 |
| 4 | 3 | 4 |
| 5 | 4 | 3 |
+---------+-------+-------+也就是學生1是第一名；學生5是第三名；...

試重新思考「名次」的定義。我們直覺上對於此詞彙的解釋是：「分數第n高者為第n名」，但這個作法則是將「第n名」界定為「有n個人(含自己)的分數跟自己相同或較高」。
依照這個想法，要排序名次，資料表就得跟自身比較。讓我們試寫「列出分數不比自己低的人」的指令，也就是：SELECT t1.student, t2.student
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score結果為：+------------+------------+
| t1.student | t2.student |
+------------+------------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 4 | 1 |
| 4 | 2 |
| 4 | 4 |
| 4 | 5 |
| 5 | 1 |
| 5 | 2 |
| 5 | 4 |
+------------+------------+
但其實我們並不需要知道「哪些人」分數不比自己低，而是要知道有「幾個人」，所以即使沒有顯示t2.student也沒有關係：SELECT t1.student
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score假設這張表叫做t3，那麼「列出分數不比自己低的人的總數」，SQL指令就是：SELECT student, COUNT(*)
FROM t3
GROUP BY student（有沒有覺得最後的指令簡潔到令人吐血？）
當然啦，通常你不需要另存t3，而會把整串步驟寫成一個指令：SELECT student, COUNT(*) AS place
FROM (
SELECT t1.student
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score
) t3
GROUP BY student得出來的就會是名次列表

而如果資料表裡面的主鍵並不是單一欄位組成，比方說：+---------+---------+-------+
| student | subject | score |
+---------+---------+-------+其中Primary Key為(student, subject)，那麼題目就變成「各學生在各科目的排名」，SQL指令為：SELECT student, subject, COUNT(*)
FROM (
SELECT t1.student, t1.subject
FROM ss AS t1, ss AS t2
WHERE t1.score <= t2.score
AND t1.subject = t2.subject
) t3
GROUP BY student, subject

上述的作法遇到考試科目同分時會有些狀況，不過由於今天我只想針對roga大的問題，也就是對primary key做排序，所以就不管了。

根據以上的作法，我們就可以輕易得知每一筆資料的排序順位，也就可以很輕易的執行相關的操作。而像是「列出各科目中考最差的五名」這樣的問題，即使每個科目的修課人數不同，也只要將「名次」的定義改成「分數不比自己高的人數」就可以輕易做到了。
回歸正題，roga大的狀況就會變成：DELETE FROM tbl
USING tbl INNER JOIN (
SELECT id, count(*) AS place
FROM (
SELECT t1.id
FROM tbl AS t1, tbl AS t2
WHERE t1.url_id = t2.url_id
AND t1.id <= t2.id
) AS t3
GROUP BY id
) AS t4
WHERE tbl.id = t4.id
AND t4.place > 1500至於哪邊用大於，哪邊用小於，可得想清楚了....

（編輯終了，本文花了我一個半小時）