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)
這樣就不會被抗議了.
在績效等等方面都可以運用.