SQL中的Dis聚合

Question

我有一张桌子

Team           Matches       Won
A                10           2
B                20           4 
C                40           10

我想将此表转换为以下内容

Team             Won/Lost         Won Lost Number
A                    Won              2
A                    Lost             8
B                    Won              4
B                    Lost             16
C                    Won              10
C                   Lost              30

提前感谢！

Answer 1

事实上，这里没有聚合-只有一个简单的减法。我会在两个查询中执行此操作，一个针对wins，另一个针对loses，并将它们与union all运算符相结合：

SELECT    team, 'Won' AS "Won/Lost", won AS "Won/Lost Number"
FROM      my_table
UNION ALL
SELECT    team, 'Lost', matches - won
FROM      my_table
ORDER BY  1 ASC, 2 DESC