mysql - 关于使用多个 SQL GROUP BY 语句的问题
问题描述
所以最终我想显示一个投手在我的棒球联盟中赢得赛扬奖的总次数,然后是他们赢得它的赛季年份,但按联盟名称排序,因为投手本可以获胜同一年不同联赛的奖项。
这就是我试图显示数据的方式:Andrew Jones (4) - 2021, 2020 (18+); 2021 年(28 岁以上);2021 (38+),因此我需要该Seasons
列吐出这样的数据:2021, 2020 (18+); 2021 年(28 岁以上);2021 (38+)
我有 3 个 mySQL 表(SEASON、LEAGUES、CY_YOUNG):
联赛
| LEAGUE_ID | LEAGUE_NAME |
| -------- | -------------- |
| 1 | 18+ |
| 2 | 28+ |
| 3 | 38+ |
| 4 | 48+ |
季节
| SEASON_ID| LEAGUE_ID | SEASON_YEAR |
| -------- | -------------| ----------- |
| 332 | 1 | 2021 |
| 333 | 2 | 2021 |
| 334 | 3 | 2021 |
| 335 | 4 | 2021 |
| 300 | 1 | 2020 |
| 301 | 2 | 2020 |
| 302 | 3 | 2020 |
| 303 | 4 | 2020 |
CY_YOUNG
| SEASON_ID | PLAYER_NAME | PLACE |
| -------- | -------------- | ----------|
| 332 | Andrew Jones | 1 |
| 332 | Mike Smith | 2 |
| 333 | Andrew Jones | 1 |
| 333 | Jacob Grimes | 2 |
| 334 | Andrew Jones | 1 |
| 334 | Travis Deane | 2 |
| 300 | Andrew Jones | 1 |
这是我当前的 SQL 查询:
SELECT PLAYER_NAME, COUNT(ID) AS TotalWins, GROUP_CONCAT(DISTINCT CONCAT(S.SEASON_YEAR,' (',L.LEAGUE_NAME)
ORDER BY S.SEASON_YEAR DESC SEPARATOR '), ') AS Seasons
FROM CY_YOUNG
JOIN SEASONS S ON S.SEASON_ID = CY_YOUNG.SeasonID
JOIN LEAGUES L ON L.LEAGUE_ID = S.LEAGUE_ID
WHERE CY_YOUNG.Place = 1
GROUP BY PLAYER_NAME
HAVING TotalWins > 1
ORDER BY TotalWins DESC;
该Seasons
专栏目前吐出这样的内容: 2021 (18+), 2020 (18+), 2021 (38+)但我希望它吐出这样的内容:2021, 2020 (18+); 2021 年(28 岁以上);2021 年(38 岁以上)。我知道我很接近所以任何帮助表示赞赏!
解决方案
您需要 2 个级别的聚合:
SELECT PLAYER_NAME,
SUM(counter) AS TotalWins,
GROUP_CONCAT(years, ' (', LEAGUE_NAME, ')' ORDER BY LEAGUE_NAME, years) AS Seasons
FROM (
SELECT PLAYER_NAME, L.LEAGUE_NAME,
COUNT(*) counter,
GROUP_CONCAT(S.SEASON_YEAR ORDER BY S.SEASON_YEAR DESC) AS years
FROM CY_YOUNG C
JOIN SEASONS S ON S.SEASON_ID = C.SEASON_ID
JOIN LEAGUES L ON L.LEAGUE_ID = S.LEAGUE_ID
WHERE C.Place = 1
GROUP BY C.PLAYER_NAME, L.LEAGUE_NAME
) t
GROUP BY PLAYER_NAME
HAVING TotalWins > 1
ORDER BY TotalWins DESC;
请参阅演示。
推荐阅读
- reactjs - 使用直接链接时如何在加载路由之前确保数据存在于redux store中?
- python - 为什么 '\n' 在 add_argument 的帮助选项中不起作用?
- python - 如何使用 Keras 多类 Sequential 模型获得一个类的单一预测?
- angular - Angular、jsPDF Autotable 和多个表格
- google-cloud-platform - 可以使用 gcloud 更改 LB 指向的实例组吗?
- asp.net - 哪些错误处理程序要添加到我在 asp.net 上的 Web 应用程序?
- time - Gitlab CI 在作业日志中显示时间
- common-workflow-language - Dockstore CWL 输出文件的奇怪行为
- c# - Nuget dll地狱:如何解决“无法加载文件或程序集'System.Threading.Tasks.Extensions,版本= 4.5.4.0”
- javascript - 如何识别 Busy Indicator 是在组件级别还是在整个 UI 级别使用来设置 aria 属性