本文轉自：i丹尼
因為要跑活動績分排行，而這個排行又是以某項欄位總和做排序 給績分，所以希望撈出的資料 在 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() 語法，可參考 這裡
上網做相關的搜尋，一開始連要搜尋怎麼的關鍵詞都摸不著頭序，就一直試，後來發現搜尋 "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 的影響而產生不定數的值。
最後，經過不同的常試修改，試出了這個方式，也就是把 @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 設定每日早上跑，去產生昨天的排行!!
以上經驗，分享給大家!!
