我想在 MySQL 中查询三个表。如下:
**Table: Leaderboard**
Name | Score
------------
James | 1
Steve | 2
Dave | 5
**Table: Actions**
Name | Action | Time
----------------------------
James | Ate an apple | 01:00
James | Kicked a dog | 02:00
Steve | Ate a dog | 03:00
Steve | Kicked a hen | 01:00
Dave | died | 02:00
**Table: Items**
Name | Item | Time
----------------------------
James | Chainsaw | 01:00
James | Hammer | 01:05
James | Crowbar | 01:10
Steve | Hammer | 02:00
Steve | Egg | 01:05
Dave | Egg | 01:05
我需要一个查询,选择每个玩家 (ORDER BY Leaderboard.score DESC) 并选择他们最新的 Action WHERE Actions.action LIKE 'Ate %',然后给出所有 Items.Item ORDER BY Time DESC
例如,输出将如下所示
**Output**
Name | Latest_Action | Items
Steve | Ate a dog | Hammer, Egg
James | Ate an apple | Crowbar, Hammer, Chainsaw
到目前为止,我已经尝试了以下查询,但它在 group_concat 中多次返回每个项目
SELECT Leaderboard.Name, Actions.*, group_concat(Items.Item)
FROM Leaderboard, Actions, Items
WHERE Items.Name = Actions.Name
AND Actions.Action LIKE 'Ate %'
AND Actions.Name IN (SELECT Name FROM Leaderboard ORDER BY SCORE DESC)
GROUP BY Leaderboard.name
非常感谢任何帮助!
SELECT Leaderboard.Name,
(SELECT Actions.Action
FROM Actions
WHERE Actions.Name = Leaderboard.Name
AND Actions.Action LIKE 'Ate%'
ORDER BY Time DESC
LIMIT 1
) AS Latest_Action,
GROUP_CONCAT(Items.Item
ORDER BY Items.Time DESC
SEPARATOR ', '
) AS Items
FROM Leaderboard
LEFT JOIN Items ON Leaderboard.Name = Items.Name
GROUP BY Leaderboard.Name
HAVING Latest_Action IS NOT NULL
ORDER BY Leaderboard.Score DESC
结果验证于SQL小提琴.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)