作者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)