mysql表正负数之和

Question

我有一列有正数和负数。是否可以对 Col1 中的所有正数、Col2 中的负数和 Col3 中的 (Col1 - Col2) 求和。然后按最后一列排序。

    Current table         New Table
    ID Score              ID   Pos  Neg   Diff            
    1   3                  3    5     0     5
    1   1                  1    4     1     3
    1  -1                  2    2     1     1
    2   1
    2  -1
    2   1
    3   3
    3   1
    3   1

这给出了总数，但我还想列出 Pos 和 Neg 数字。

SELECT ID, SUM(Score) as total FROM results 
GROUP BY ID ORDER BY total DESC

Answer 1

只需对正数和负数使用带有单独条件聚合的标准数据透视查询。

SELECT
    ID,
    SUM(CASE WHEN Score >= 0 THEN Score    ELSE 0 END) AS Pos,
    SUM(CASE WHEN Score <  0 THEN -1*Score ELSE 0 END) AS Neg,
    SUM(Score) AS Diff
FROM results
GROUP BY ID
ORDER BY ID

Demo