mysql - 在mysql中加入3个字段

Question

我有一个使用 uin 的 sql 查询

    SELECT Sum(scores_ofexpert.score) FROM scores_ofexpert
    WHERE scores_ofexpert.user_id = '30' AND scores_ofexpert.score > '0'
    UNION
    SELECT Sum(scores_ofexpert.score) FROM scores_ofexpert
    WHERE scores_ofexpert.user_id = '30' AND scores_ofexpert.score < '0'
    UNION
    SELECT Sum(scores_ofexpert.score) FROM scores_ofexpert
    WHERE scores_ofexpert.user_id = '30'

这个提取用户的负分数总和，正分数总和和所有分数总和；像这样

 -------------
 |-10        |
 -------------
 |30         |
 -------------
 |20         |
 -------------

但我想要这样的结果

 -------------------------------------
 |n_sum      |p_sum      |sum        |
 -------------------------------------    
 |-10        |30         |20         |
 -------------------------------------

怎么做到的？

Answer 1

你可以使用CASE

 SELECT
 SUM(CASE WHEN s.score < 0 THEN s.score ELSE 0 END) n_sum,
 SUM(CASE WHEN s.score > 0 THEN s.score ELSE 0 END) p_sum,
 SUM(s.score) `sum`
 FROM scores_ofexpert s
   WHERE s.user_id = '30'