MySQL SELECT来自多个表,多个GROUP BY和group_concat？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL SELECT来自多个表,多个GROUP BY和group_concat？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1656字，纯文字阅读大概需要3分钟。

内容图文

我有三个表,我想在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

任何帮助非常感谢！

解决方法:

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 Fiddle中验证.

内容总结

以上是互联网集市为您收集整理的MySQL SELECT来自多个表,多个GROUP BY和group_concat？全部内容，希望文章能够帮你解决MySQL SELECT来自多个表,多个GROUP BY和group_concat？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端