2013年9月26日 星期四

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 設定每日早上跑,去產生昨天的排行!!
以上經驗,分享給大家!!

沒有留言: