將要生成的最終結果:
該表會根據用戶選擇某個組別,以及某個賽事的當下賽季來顯示不同內容,即是說,該查詢應該有如下輸入參數:
組別的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_id
FROM yellow_records
RIGHT JOIN red_records ON yellow_records.player_id = red_records.player_id
UNION
SELECT yellow_records.yellow_card,
red_records.red_card,
yellow_records.player_id
FROM yellow_records
LEFT 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_id
FROM yellow_records;
CREATE TEMPORARY TABLE red_temp
SELECT red_card, player_id
FROM red_records;
CREATE TEMPORARY TABLE join_table_1
SELECT yellow_temp.yellow_card, red_temp.red_card, yellow_temp.player_id
FROM yellow_temp
LEFT 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_id
FROM yellow_temp
RIGHT 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_1
UNION
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_1
SELECT yellow_records.yellow_card, red_records.red_card,
yellow_records.player_id, yellow_records.t_name, yellow_records.first_name, yellow_records.last_name
FROM yellow_records
LEFT JOIN red_records ON yellow_records.player_id = red_records.player_id;
CREATE TEMPORARY TABLE join_table_2
SELECT yellow_records.yellow_card, red_records.red_card,
red_records.player_id, red_records.t_name, red_records.first_name, red_records.last_name
FROM yellow_records
RIGHT JOIN red_records ON yellow_records.player_id = red_records.player_id;
SELECT * FROM join_table_1
UNION
SELECT * 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