【視圖】罰牌記錄：使用臨時表，UNION與JOIN將Match_Event表中的紅牌黃牌記錄分別統計出來

將要生成的最終結果：

該表會根據用戶選擇某個組別，以及某個賽事的當下賽季來顯示不同內容，即是說，該查詢應該有如下輸入參數：

組別的id(group id)；

賽季的id(season id)；

數據表的E-R圖：

該查詢的主要對象為‘match_events’，但是對其記錄將會根據參數進行過濾：

首先是球隊需是屬於所查詢的組別：

AND gt.t_id = me.t_id   AND gt.g_id = 參數（組別id）

其次，該罰牌事件記錄所屬的比賽應該是指定賽季內的比賽，賽季(seasons)包含有賽程(matchday)，賽程(matchday)則直接包含比賽(match)：

AND m.id = me.match_id   AND m.m_id IN (  參數（根據賽季id查詢出的所有賽程id）  )

另外需要查詢出的數據還包括球員姓名以及球隊姓名，由此得出統計黃牌記錄（e_id = 1）的查詢SQL語句：

SELECT SUM(me.ecount) AS yellow_card, me.player_id, t.t_name, pl.first_name, pl.last_name   FROM jos_bl_match_events AS me,   jos_bl_teams AS t,   jos_bl_players AS pl,  jos_bl_grteams AS gt,  jos_bl_match AS m  WHERE me.e_id = 1   AND me.t_id = t.id   AND me.player_id = pl.id   AND gt.t_id = me.t_id   AND gt.g_id = 4   AND m.id = me.match_id   AND m.m_id = 1   GROUP BY me.player_id;

（賽程參數有根據情況簡化）結果為：

統計紅牌記錄（e_id = 2）的查詢SQL語句：

SELECT SUM(me.ecount) AS red_card, me.player_id, t.t_name, pl.first_name, pl.last_name   FROM jos_bl_match_events AS me,   jos_bl_teams AS t,   jos_bl_players AS pl,  jos_bl_grteams AS gt,  jos_bl_match AS m  WHERE me.e_id = 2   AND me.t_id = t.id   AND me.player_id = pl.id   AND gt.t_id = me.t_id   AND gt.g_id = 4   AND m.id = me.match_id   AND m.m_id = 1   GROUP BY me.player_id;

（賽程參數有根據情況簡化）結果為：

分析：關鍵任務是將兩次查詢所得的表整合為一張表，新表內球員id為唯一值，黃牌記錄與紅牌記錄各自作為獨立的字段(field)。

在整合中將同一字段變成兩個不同的列

爲了方便下一步研究，創建兩個表來分別模擬兩次查詢結果：

在創建兩個表之後，執行如下查詢：

SELECT yellow_records.yellow_card, red_records.red_card,red_records.player_idFROM yellow_recordsRIGHT JOIN red_records ON yellow_records.player_id = red_records.player_idUNION SELECT yellow_records.yellow_card, red_records.red_card,yellow_records.player_idFROM yellow_recordsLEFT JOIN red_records ON yellow_records.player_id = red_records.player_id

所得結果為：

由此可見，所需的操作大致是：

（黃牌記錄表 LEFT JOIN 紅牌記錄表）UNION（黃牌記錄表 RIGHT JOIN 紅牌記錄表）

因為UNION不會顯示重複的記錄，所以剛好得到的結果中不會有重複。（如果用UNION ALL將會看到所得結果中的第一行會出現兩次）。

在MySQL中，‘LEFT JOIN’或‘RIGHT JOIN’操作中，‘ON’語句後面不可以只是引用用來聯合的字段，比如如下寫法將不能夠運行：

ON 後面可以跟的語句必須是 table1.field = table2.field。所以在MySQL裏面，不能夠直接基於上面設計好的SELECT查詢，再進行JOIN與UNION操作。

如下寫法甚至不能通過語法檢查：

於是，我發現需要使用臨時表（temporary table）。

在模擬表上對臨時表的實驗

實驗一：

（臨時表1 LEFT JOIN 臨時表2） UNION （臨時表1 RIGHT JOIN 臨時表2）

上述SQL將收到如下錯誤提示：

實驗二：

創建臨時表3 =（臨時表1 LEFT JOIN 臨時表2）；

創建臨時表4 = （臨時表1 RIGHT JOIN 臨時表2）；

臨時表3 UNION 臨時表4

SQL語句為：

CREATE TEMPORARY TABLE yellow_temp SELECT yellow_card, player_idFROM yellow_records;CREATE TEMPORARY TABLE red_temp SELECT red_card, player_idFROM red_records;CREATE TEMPORARY TABLE join_table_1SELECT yellow_temp.yellow_card, red_temp.red_card, yellow_temp.player_idFROM yellow_tempLEFT JOIN red_temp ON yellow_temp.player_id = red_temp.player_id;CREATE TEMPORARY TABLE join_table_2 SELECT yellow_temp.yellow_card, red_temp.red_card, red_temp.player_idFROM yellow_tempRIGHT JOIN red_temp ON red_temp.player_id = yellow_temp.player_id;SELECT join_table_1.yellow_card, join_table_1.red_card, join_table_1.player_id FROM join_table_1UNION SELECT join_table_2.yellow_card, join_table_2.red_card, join_table_2.player_id FROM join_table_2;

在phpMyAdmin中得到結果為：

至此，終於得到期望的結果。所以結論是，在MySQL里，臨時表不可以用來進行嵌套的聯合查詢。

回到初始的案例，重新設計最終的SQL查詢語句

CREATE TEMPORARY TABLE yellow_records SELECT SUM(me.ecount) AS yellow_card, me.player_id, t.t_name, pl.first_name, pl.last_name   FROM jos_bl_match_events AS me,   jos_bl_teams AS t,   jos_bl_players AS pl,  jos_bl_grteams AS gt,  jos_bl_match AS m  WHERE me.e_id = 1   AND me.t_id = t.id   AND me.player_id = pl.id   AND gt.t_id = me.t_id   AND gt.g_id = 4   AND m.id = me.match_id   AND m.m_id = 1   GROUP BY me.player_id;CREATE TEMPORARY TABLE red_records SELECT SUM(me.ecount) AS red_card, me.player_id, t.t_name, pl.first_name, pl.last_name   FROM jos_bl_match_events AS me,   jos_bl_teams AS t,   jos_bl_players AS pl,  jos_bl_grteams AS gt,  jos_bl_match AS m  WHERE me.e_id = 2   AND me.t_id = t.id   AND me.player_id = pl.id   AND gt.t_id = me.t_id   AND gt.g_id = 4   AND m.id = me.match_id   AND m.m_id = 1   GROUP BY me.player_id;CREATE TEMPORARY TABLE join_table_1SELECT yellow_records.yellow_card, red_records.red_card, yellow_records.player_id, yellow_records.t_name, yellow_records.first_name, yellow_records.last_nameFROM yellow_recordsLEFT JOIN red_records ON yellow_records.player_id = red_records.player_id;CREATE TEMPORARY TABLE join_table_2SELECT yellow_records.yellow_card, red_records.red_card, red_records.player_id, red_records.t_name, red_records.first_name, red_records.last_nameFROM yellow_recordsRIGHT JOIN red_records ON yellow_records.player_id = red_records.player_id;SELECT * FROM join_table_1 UNIONSELECT * FROM join_table_2;

在phpMyAdmin中執行，得到結果：

在Joomla!1.5 中使用創建臨時表的查詢

從實驗來看，Joomla!不支持帶有臨時表的查詢。在之前創建的test視圖的controller文件中加入：

$db= & JFactory::getDBO();$query = "CREATE TEMPORARY TABLE #__temp_polls SELECT * FROM #__polls; SELECT * FROM #__temp_polls";$db->setQuery($query);$panelty_records = $db->loadObjectList();$errmsg = $db->getErrorMsg();//dump($panelty_records, 'panelty');//dump($errmsg, 'errmsg');

得到的查詢結果為‘null’，而錯誤信息為：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM jos_temp_polls' at line 1 SQL=CREATE TEMPORARY TABLE jos_temp_polls SELECT * FROM jos_polls; SELECT * FROM jos_temp_polls

在Joomla!官方論壇po的發問帖子：http://forum.joomla.org/viewtopic.php?f=428&t=669782

REFs:

http://zhidao.baidu.com/question/111380603

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

http://docs.joomla.org/JDatabase/1.5