作者jiyon (愛)
看板Database
標題[SQL ] MySQL請問同時撈出任務基本資訊與執行記錄最後一筆的方法
時間Sat Feb 26 14:34:22 2011
資料庫版本 MySQL 5.1
說明：假設賽車遊戲中各個賽道的基本資訊存在「任務」表格
，而每次跑某一個賽道的記錄存在「記錄」表格，如下表所示：
mission(任務) record(記錄)
-------------------------------- ----------------------------------------
ai id(PK) name length level ai id(PK) m_id(FK) result spend_time
賽道名 距離 難度 結果名次 花費時間
-------------------------------- ----------------------------------------
其中ai是AUTO_INCREMENT欄位、id是UUID()產生的主鍵欄位
record.m_id是參照到mission.id的外來鍵
result是INT型態，表示第幾名
每筆記錄只會對應到一個任務，而一個任務會有多個跑的記錄，也可能沒有(還沒跑)
測試資料如下：(id部份有簡化)
mission(任務)
--------------------------------
ai id(PK) name length level
1 d482 鈴鹿賽道 28 2
2 d6b0 筑波賽道 36 4
3 d7a1 茂木賽道 20 1
--------------------------------
record(記錄)
----------------------------------------
ai id(PK) m_id(FK) result spend_time
1 7d24 d482 1 00:04:57 (對應到鈴鹿賽道)
2 7dfa d7a1 8 00:10:23 (對應到茂木賽道)
3 51d8 d482 12 00:12:28 (對應到鈴鹿賽道)
4 17d3 d7a1 1 00:06:11 (對應到茂木賽道)
5 5755 d7a1 1 00:05:59 (對應到茂木賽道)
----------------------------------------
UI(使用者介面)想要呈現，各個賽道的基本資訊，以及各賽道最後一次跑的資訊
UI想要顯示的欄位與資料：
賽道名稱 距離 難度 最後一次名次 最後一次花費時間
====================================================
茂木賽道 20 1 1 00:05:39
筑波賽道 36 4
鈴鹿賽道 28 2 12 00:12:28
目前使用的SQL如下：
(一)先列出賽道基本資訊(後建立的排在前面)
SELECT id, name, length, level
FROM mission
ORDER BY ai DESC;
(二)再由任務主鍵找出最新一筆跑的記錄
SELECT result, spend_time
FROM record
WHERE m_id = @id -- 由(一)取得的mission.id
ORDER BY ai DESC
LIMIT 1;
實作上，我們是DB與UI分工，我是將(一)(二)寫成兩個stored procedure
讓負責UI的同學來呼叫，就是將(一)取得的mission.id存入陣列，再呼叫(二)
現在問題來了，如果UI想要各進階一點，將結果名次納入查詢條件中，
例如名次是第一名的賽道資料才列出，由於我這邊SQL不知道怎麼下，
只好請UI的同學還是用(一)(二)撈出所有資料，再過濾出 result = 1 的資料
UI預計得到結果
賽道名稱 距離 難度 最後一次名次 最後一次花費時間
====================================================
茂木賽道 20 1 1 00:05:39
想請問一下，如果我還是想讓資料過濾的部份讓資料庫來作的話，
(1)需要怎麼下SQL
(2)或是目前設計的資料表需要增加或是修改嗎
我有試過子查詢：
SELECT m.name, m.length, m.level,
(
SELECT r.result
FROM record r
WHERE r.m_id = m.id
ORDER BY ai DESC
LIMIT 1
) AS result,
(
SELECT r.spend_time
FROM record r
WHERE r.m_id = m.id
ORDER BY ai DESC
LIMIT 1
) AS spend_time
FROM mission m
ORDER BY ai DESC;
取得結果如下：
name length level result spend_time
茂木賽道 20 1 1 00:05:59
筑波賽道 36 4 \N \N
鈴鹿賽道 28 2 12 00:12:28
可以將(一)(二)整合在一起，但是不知道要下WHERE條件撈出result = 1的資料
(可以的話希望盡量不用子查詢)
另外也試了左結合(沒有跑的記錄的賽道也列出，再篩選)
SELECT m.name, m.length, m.level, r.result, r.spend_time
FROM mission m LEFT JOIN record r ON m.id = r.m_id
WHERE r.result = 1
ORDER BY m.ai DESC;
取得結果如下：
name length level result spend_time
茂木賽道 20 1 1 00:06:11
茂木賽道 20 1 1 00:05:59
鈴鹿賽道 28 2 1 00:04:57
但是這樣會撈出各賽道的所有記錄(例如茂木賽道就撈出兩筆)
只撈各賽道的最後一筆記錄的條件，就不知道怎麼下SQL了
想請大家指點迷津。感謝。
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 211.75.30.164
※ 編輯: jiyon 來自: 211.75.30.164 (02/26 14:37)
※ 編輯: jiyon 來自: 211.75.30.164 (02/26 15:23)
※ 編輯: jiyon 來自: 211.75.30.164 (02/26 15:26)