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

只需对正数和负数使用标准的pivot查询和单独的条件聚合。

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

​