Oracle 有一個虛擬欄位rownum,讓許多操作有極大的便利性.
今天將介紹在MySQL產生rownum的方法,以及排名的方法.
MySQL一般情況下是沒有rownum的,可以上patch來加強,但是
這個上patch相對比較難,在此先不討論.
先來看一個簡單的Table.

CREATE TABLE ithelp1010a(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(10) NOT NULL,
NumAV INT UNSIGNED NOT NULL);

INSERT INTO ithelp1010a(name, NumAV) VALUES
('泰大', 99999),
('蒼鴻', 250),
('榮哥', 10),
('太陽大', 75),
('小雨', 12345);

依照擁有的影片數目來排名,用 ORDER BY 就可以了.

SELECT name, NumAV
  FROM ithelp1010a
 ORDER BY NumAV DESC;

+-----------+-------+
| name      | NumAV |
+-----------+-------+
| 泰大      | 99999 |
| 小雨      | 12345 |
| 蒼鴻      |   250 |
| 太陽大    |    75 |
| 榮哥      |    10 |
+-----------+-------+
5 rows in set (0.00 sec)

但是人們喜歡看到排名,可是MySQL本身並未直接有這樣功能,
我們需要設法加強.

首先來看產生 rownum 的一種方法.

SELECT  @rownum := @rownum + 1 AS 'rownum'
       ,name
       ,NumAV
  FROM  ithelp1010a
       ,(SELECT @rownum := 0) b
 ORDER BY NumAV DESC;

+--------+-----------+-------+
| rownum | name      | NumAV |
+--------+-----------+-------+
|      1 | 泰大      | 99999 |
|      2 | 小雨      | 12345 |
|      3 | 蒼鴻      |   250 |
|      4 | 太陽大    |    75 |
|      5 | 榮哥      |    10 |
+--------+-----------+-------+
5 rows in set (0.00 sec)

可以輕易的依照指定的順序,依序遞增.
但是這樣作只是依序遞增,不是真正的排名.
Oracle 有 RANK(), DENSE_RANK() 來處理排名,
MySQL就要自己變化了.

先來將太陽大的影片數量改成跟蒼鴻的一樣.

UPDATE ithelp1010a
   SET NumAV = 250
 WHERE name = '太陽大';

若按照之前的方法,會是以下結果

SELECT  @rownum := @rownum + 1 AS 'rownum'
       ,name
       ,NumAV
  FROM  ithelp1010a
       ,(SELECT @rownum := 0) b
 ORDER BY NumAV DESC;

+--------+-----------+-------+
| rownum | name      | NumAV |
+--------+-----------+-------+
|      1 | 泰大      | 99999 |
|      2 | 小雨      | 12345 |
|      3 | 蒼鴻      |   250 |
|      4 | 太陽大    |   250 |
|      5 | 榮哥      |    10 |
+--------+-----------+-------+

可以看到AV譜排名 3,4 大有問題,這樣有人會不服氣的.

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

+------+-----------+-------+
| Rank | name      | NumAV |
+------+-----------+-------+
|    1 | 泰大      | 99999 |
|    2 | 小雨      | 12345 |
|    3 | 蒼鴻      |   250 |
|    3 | 太陽大    |   250 |
|    4 | 榮哥      |    10 |
+------+-----------+-------+

配合昨天介紹的 HAVING,可以很容易找出AV譜排名前三名.
SELECT a.rank AS 'Rank'
     , a.name
     , NumAV
  FROM (SELECT name
             , NumAV
             , @prev := @curr
             , @curr := NumAV
             , @rank := IF(@prev = @curr, @rank, @rank + 1) AS rank
          FROM ithelp1010a
             , (SELECT @curr := null
                     , @prev := null
                     , @rank := 0) s
         ORDER BY NumAV DESC) a
 HAVING Rank <= 3; 

+------+-----------+-------+
| Rank | name      | NumAV |
+------+-----------+-------+
|    1 | 泰大      | 99999 |
|    2 | 小雨      | 12345 |
|    3 | 蒼鴻      |   250 |
|    3 | 太陽大    |   250 |
+------+-----------+-------+
4 rows in set (0.00 sec)

這樣就不會被抗議了.

在績效等等方面都可以運用.