SQL Server 按连续列分组问题

发布于 2025-01-17 06:01:54 字数 734 浏览 0 评论 0原文

我尝试使用 concat 列对查询结果进行分组，但我坚持这样做。

所以这是我的查询：

SELECT 
    CONCAT (a.athleteLastName, ', ', a.athleteFirstName, ' (', a.athletePosition, ')') AS "Athlete",
    COUNT(M.awardName) AS "Number of Awards Won" 
FROM 
    award m
FULL OUTER JOIN 
    winner w ON m.awardId = w.awardId
FULL OUTER JOIN  
    athlete a ON w.athleteId = a.athleteId
GROUP BY 
    M.awardId, athleteLastName, a.athleteFirstName,a.athletePosition
ORDER BY 
    M.awardId DESC, Athlete;

这是结果：

Trout, Mike (OF)    1
Arenado, Nolan (3B) 4
Arenado, Nolan (3B) 7
Trout, Mike (OF)    3

但我想按玩家对奖励计数进行分组，它应该像这样结束：

Arenado, Nolan (3B) 13
Trout, Mike (OF)    4

原文

I am trying to group by the results of the query with a concat column, but I am stuck with it.

So this is my query:

SELECT 
    CONCAT (a.athleteLastName, ', ', a.athleteFirstName, ' (', a.athletePosition, ')') AS "Athlete",
    COUNT(M.awardName) AS "Number of Awards Won" 
FROM 
    award m
FULL OUTER JOIN 
    winner w ON m.awardId = w.awardId
FULL OUTER JOIN  
    athlete a ON w.athleteId = a.athleteId
GROUP BY 
    M.awardId, athleteLastName, a.athleteFirstName,a.athletePosition
ORDER BY 
    M.awardId DESC, Athlete;

This is the result:

Trout, Mike (OF)    1
Arenado, Nolan (3B) 4
Arenado, Nolan (3B) 7
Trout, Mike (OF)    3

But I want to group the count of awards by the player, and it should end like this:

Arenado, Nolan (3B) 13
Trout, Mike (OF)    4

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぺ禁宫浮华殁 2025-01-24 06:01:54

Arenado、Nolan (3B) 如何成为 13？如果只是简单地总结奖项，您可以使用子查询，可以尝试以下操作：

select "Athlete", sum("Number of Awards Won") as awards
from
(SELECT CONCAT (a.athleteLastName, ', ',a.athleteFirstName,' (',a.athletePosition,')') AS "Athlete" ,count(M.awardName) AS "Number of Awards Won" FROM award m
        FULL OUTER JOIN winner w ON m.awardId = w.awardId
        FULL OUTER JOIN  athlete a ON w.athleteId = a.athleteId
        GROUP BY M.awardId, athleteLastName, a.athleteFirstName,a.athletePosition
        ORDER BY M.awardId DESC, Athlete )  as a group by 1;

How can Arenado, Nolan (3B) becomes 13? If simply summing up the awards, you can use subquery and can try this:

select "Athlete", sum("Number of Awards Won") as awards
from
(SELECT CONCAT (a.athleteLastName, ', ',a.athleteFirstName,' (',a.athletePosition,')') AS "Athlete" ,count(M.awardName) AS "Number of Awards Won" FROM award m
        FULL OUTER JOIN winner w ON m.awardId = w.awardId
        FULL OUTER JOIN  athlete a ON w.athleteId = a.athleteId
        GROUP BY M.awardId, athleteLastName, a.athleteFirstName,a.athletePosition
        ORDER BY M.awardId DESC, Athlete )  as a group by 1;

回复收藏 0 原文

~没有更多了~