mysql - 获取特定人员的列中最常见的值
问题描述
所以我有一张表格,用来收集每个人的数据。
+--------+-------+--------+---------+----------------+
| player | kills | deaths | assists | champSelection |
+--------+-------+--------+---------+----------------+
| Bob | 1 | 3 | 4 | Horse |
+--------+-------+--------+---------+----------------+
| Bob | 2 | 7 | 5 | Horse |
+--------+-------+--------+---------+----------------+
| Jake | 5 | 5 | 5 | Dog |
+--------+-------+--------+---------+----------------+
| Marie | 2 | 3 | 4 | Dog |
+--------+-------+--------+---------+----------------+
| Marie | 1 | 1 | 9 | Horse |
+--------+-------+--------+---------+----------------+
| Marie | 6 | 7 | 2 | Dog |
+--------+-------+--------+---------+----------------+
我正在跑步
SELECT player, sum(kills), sum(deaths), sum(assists)
FROM playerTable
GROUP BY player
ORDER BY player ASC;
我将如何在我的查询中添加他们最常见的 champSelection 是什么?我正在尝试将我的表格显示为
+--------+-------+--------+---------+----------------+
| player | kills | deaths | assists | champSelection |
+--------+-------+--------+---------+----------------+
| Bob | 3 | 10 | 9 | Horse |
+--------+-------+--------+---------+----------------+
| Jake | 5 | 5 | 5 | Dog |
+--------+-------+--------+---------+----------------+
| Marie | 9 | 11 | 15 | Dog |
+--------+-------+--------+---------+----------------+
解决方案
尝试这个:
SELECT player, SUM(kills), SUM(deaths), SUM(assists), frequency
FROM
(
SELECT player, SUM(kills) AS kills, SUM(deaths) AS deaths, SUM(assists) AS assists, champSelection, COUNT(*) AS frequency
FROM playerTable
GROUP BY player, champSelection
ORDER BY frequency DESC
) AS inner_table
GROUP BY player
推荐阅读
- docker - Hyperledger Fabric 对等容器未出现
- javascript - 如何将逐步键和值添加到三个不同的选择选项值
- symfony - 将 Twig 全局变量导入 symfony 控制器
- world-of-warcraft - 客户端卡在“身份验证”阶段
- apache-spark - Apache Spark 不使用来自 Hive 分区外部表的分区信息
- rust - 如何使用 rust 语言在图像中绘制矩形
- ruby-on-rails - 如何消除这个 n+1 查询?
- reactjs - 具有动态主题值的 Google Recaptcha 组件 - Next.js
- python - 为什么我的 CSS 样式不会显示在烧瓶应用程序上
- c++ - 遍历对象列表