SQL 求和按相同标识符分组的两个不同列中的字​​段 [英] SQL sum a field grouped by same identifier in two different columns

问题描述

该表有 4 列 Match、Winning_Player_ID、Losing_Player_ID、Quantity_Points_Exchanged_for_that_match.我想在单个查询中显示每个玩家在所有比赛中赢得和获得的总积分数.

The table has 4 columns Match, Winning_Player_ID, Losing_Player_ID, Quantity_Points_Exchanged_for_that_match. I would like to in a single query show the total number of points each player won and earned over all matches.

这是表

M WIN LOSE QTY  
1 100 201 10  
2 201 100 05  
3 100 201 05  
4 302 100 05  

对于输出，我希望在单个查询中以这种方式总计，但无法弄清楚.

For output I would like total it in this way in a single query and cannot figure it out.

ID WIN LOSE
100 15 10
201 05 15
302 05 00

推荐答案

SELECT p.player ID,
       (SELECT SUM(QTY) FROM tbl WHERE WIN = p.player) WIN,
       (SELECT SUM(QTY) FROM tbl WHERE LOSE = p.player) LOSE
FROM
    (SELECT DISTINCT WIN player FROM tbl
    UNION
    SELECT DISTINCT LOSE FROM tbl) p

这篇关于SQL 求和按相同标识符分组的两个不同列中的字​​段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！