mysql group by 多表_MySQL SELECT来自多个表,多个GROUP BY和group_concat？

我有三个表,我想在MySQ中查询.如下：

**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中多次返回每个Item

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

任何帮助非常感谢！