定時統計個欄位拍序列表結果，另存table執行卡住

mysql

codegeass 2014-08-26 11:26:02 ‧ 2655 瀏覽

分享至

小弟想將班上個科系分數做成績排名直接查詢並建置一個新表放入
但小弟我光使用的查詢方式，就已經讓執行續卡住了~!!!
但減少到只處理5個學科以下還可以運作，一往上追加就.......
不知是否用錯方法讓執行卡住?

SELECT
main.id, s1.國語, s2.英語，......sx.xx
FROM
(SELECT id FROM CLASS_A ORDER BY id) as main
JOIN 
(SELECT @rownum := @rownum +1 AS 'rownum', id, 國語 FROM CLASS_A, (SELECT @rownum :=0) a ORDER BY 國語 DESC) AS s1
ON main.uid = s1.uid
JOIN 
(SELECT @rownum := @rownum +1 AS 'rownum', id, 英語 FROM CLASS_A, (SELECT @rownum :=0) b ORDER BY 英語 DESC) AS s2
ON main.uid = s2.uid
        .
        .
        .
        .
(SELECT @rownum := @rownum +1 AS 'rownum', id, xx FROM CLASS_A, (SELECT @rownum :=0) x  ORDER BY xx DESC) AS sx
ON main.uid = sx.uid
+------------+----------+----------+-----------------+----------+----------+-----------------+
TABLE CLASS_A
+------------+----------+----------+-----------------+----------+----------+-----------------+
|         學號|       國語|       英語|             數學|       體育|       社會|             自然|  
+------------+----------+----------+-----------------+----------+----------+-----------------+
| 49421101   |       30 |       90 |             100 |       30 |       40 |                0| 
| 49421102   |       50 |       60 |              50 |       30 |       70 |               50|  
| 49421103   |       70 |       80 |               0 |       30 |       90 |               85| 
+------------+----------+----------+-----------------+----------+----------+-----------------+
 
+------------+----------+----------+-----------------+----------+----------+-----------------+
TABLE TMP_CLASS_A
+------------+----------+----------+-----------------+----------+----------+-----------------+
+------------+----------+----------+-----------------+----------+----------+-----------------+
|         學號|       國語|       英語|             數學|       體育|       社會|             自然|  
+------------+----------+----------+-----------------+----------+----------+-----------------+
| 49421101   |       3 |       1 |                 1 |        1 |        3 |                0| 
| 49421102   |       2 |       3 |                 2 |        2 |        2 |                1|  
| 49421103   |       1 |       2 |                 3 |        3 |        1 |                2| 
+------------+----------+----------+-----------------+----------+----------+-----------------+