MySQL SELECT从多个表,多个GROUP BY和group_concat? [英] MySQL SELECT from multiple tables, multiple GROUP BY and group_concat?
本文介绍了MySQL SELECT从多个表,多个GROUP BY和group_concat?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有三个要在MySQ中查询的表.如下:
I have three tables which I want to query in MySQ. As follows:
**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
我需要一个查询来选择每个玩家(按Leaderboard.score DESC排序)并选择他们最新的Action WHERE Actions.action LIKE'Ate%',然后给出所有Items.Item ORDER BY Time DESC
I need a query which selects each player (ORDER BY Leaderboard.score DESC) and selects their latest Action WHERE Actions.action LIKE 'Ate %', and then gives all Items.Item ORDER BY Time DESC
例如,输出看起来像这样
So for example, the output would look like this
**Output**
Name | Latest_Action | Items
Steve | Ate a dog | Hammer, Egg
James | Ate an apple | Crowbar, Hammer, Chainsaw
到目前为止,我已经尝试了以下查询,但是它在group_concat中多次返回了每个项目
So far I have tried the following query but it returns each Item multiple times in the 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小提琴中验证的结果.
这篇关于MySQL SELECT从多个表,多个GROUP BY和group_concat?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文