MySQL 如何用 SELECT 查詢 產生一個自動遞增的序號欄位

因為要跑活動績分排行，而這個排行又是以某項欄位總和做排序 給績分，所以希望撈出的資料 在 SUM, GROUP 及 ORDER 後，還可以再依序給一個排行績分的欄位。

因為是持續三週的活動，且每天都有每日的績分排行(前20名的績分依序就是20,19,18...到1分)，每週還有週排行，週排行就是將每個Player該週的績分加總排序即可。

所以用了一個活動資料表，來記錄每個玩家每日的績分:

CREATE TABLE `tbl_event_A` (
  `sn` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'SN',
  `playerid` int(11) unsigned NOT NULL COMMENT 'PlayerID',
  `logday` int(11) unsigned NOT NULL COMMENT '記錄日期',
  `winpoint` int(11) NOT NULL COMMENT '日總贏點數',
  `score` int(11) NOT NULL COMMENT '日績分',
  `week` tinyint(3) NOT NULL COMMENT '週次',
  PRIMARY KEY (`sn`),
  KEY `logdate` (`logday`)
) ENGINE=MyISAM;

希望從原始資料表撈出的排序資料及績分，可以在一個 Query 動作中完成，所以會需要在 Select 動作中，帶出一個有遞增績分欄位。

上網做相關的搜尋，一開始連要搜尋怎麼的關鍵詞都摸不著頭序，就一直試，後來發現搜尋 "select 查詢 自動 序號" 這樣的結果最多相關的結果。
有不少回應是 MS SQL 本身才支援的語法，像是 RANK() OVER() ROW_NUMBER() 語法，可參考 這裡

反而 MySQL 的討論不多，後來後搜尋英文 mysql select auto increment number ，才有較多的
討論。

原來是利用 MySQL 中臨時(暫時)變數的方式來做遞增，簡單範例如下：

SELECT @s:=@s+1 serial_number, student_id, student_name, student_avg
FROM students, (SELECT @s:= 0) AS s
WHERE student_avg > 4;

於是馬上就把它套用到我的查詢裡面：

SELECT @SN:=@SN+1 as Score, PlayerID, SUM(winpoint) as WinPoint 
FROM tbl_dailyplayerpoint,(SELECT @SN:=0) as Dummy 
WHERE logday='$Yesterday' 
GROUP BY PlayerID 
ORDER BY WinPoint DESC 
LIMIT 20;

試了發現的確可以產生一個遞增的欄位，但放在我的 query 中，經過 SUM, GROUP 及 ORDER BY 後，帶出的數值很奇怪，是一些很大的數字，且沒有規律。

+-------+----------+----------+
| Score | PlayerID | WinPoint |
+-------+----------+----------+
|    31 |        4 | 20441389 |
|    60 |       31 | 12158264 |
|    35 |       10 | 11551464 |
|    41 |       23 |  8594067 |
|    46 |       34 |  7555544 |
|    29 |        1 |  7475172 |
|    42 |       24 |  6409297 |
|    62 |       11 |  6393142 |
|    78 |       54 |  5791644 |
|    45 |       33 |  5757180 |
|    47 |       35 |  5689378 |
|    70 |       44 |  5649346 |
|    34 |        8 |  5590604 |
|    86 |       56 |  5470747 |
|    44 |       32 |  5289877 |
|    40 |       22 |  5080401 |
|    81 |       22 |  4591810 |
|     2 |       25 |  4446734 |
|    75 |       93 |  4431110 |
|    63 |       68 |  4430356 |
+-------+----------+----------+

於是在想，是不是因為這個臨時的變數在經過 GROUP 後，因為不同的資料量而被加總了不同次而不同，不是想像中的由小到大。

最後，經過不同的常試修改，試出了這個方式，也就是把 @SN:=@SN+1 移出原始query，把原查詢變成一個子查詢，@SN:=@SN+1放到外層，這樣 @SN 就不會受到 GROUP 的影響而產生不定數的值。

SELECT 21-(@SN:=@SN+1) as Score, PlayerID, WinPoint 
FROM (SELECT PlayerID, SUM(winpoint) as WinPoint 
      FROM tbl_dailyplayerpoint 
      WHERE logday='$Yesterday' 
      GROUP BY PlayerID 
      ORDER BY WinPoint DESC 
      LIMIT 20) as Dummy1, 
(SELECT @SN:=0) as Dummy2;

撈出的結果看來就是我想要的!!

+-------+----------+----------+
| Score | PlayerID | WinPoint |
+-------+----------+----------+
|    20 |        4 | 20441389 |
|    19 |       31 | 12158264 |
|    18 |       10 | 11551464 |
|    17 |       23 |  8594067 |
|    16 |       34 |  7555544 |
|    15 |        1 |  7475172 |
|    14 |       24 |  6409297 |
|    13 |       11 |  6393142 |
|    12 |       54 |  5791644 |
|    11 |       33 |  5757180 |
|    10 |       35 |  5689378 |
|     9 |       44 |  5649346 |
|     8 |        8 |  5590604 |
|     7 |       56 |  5470747 |
|     6 |       32 |  5289877 |
|     5 |       22 |  5080401 |
|     4 |       22 |  4591810 |
|     3 |       25 |  4446734 |
|     2 |       93 |  4431110 |
|     1 |       68 |  4430356 |
+-------+----------+----------+

於是我就可以在一個 query 中，直接撈出並新增到所建立的活動資料表中了!!

 
INSERT INTO tbl_event_A (score,playerid,winpoint,logday,week) 
SELECT 21-(@SN:=@SN+1), PlayerID, WinPoint,'$Yesterday','$week' 
FROM (SELECT PlayerID, SUM(winpoint) as WinPoint 
      FROM tbl_dailyplayerpoint 
      WHERE logday='$Yesterday' 
      GROUP BY PlayerID 
      ORDER BY WinPoint DESC 
      LIMIT 20) as Dummy1,
(SELECT @SN:=0) as Dummy2;

寫好的 PHP 用 cron table 設定每日早上跑，去產生昨天的排行!!
以上經驗，分享給大家!!