MySQL select 根据其他字段计算 2 个字段

Question

假设我有一张这样的 table

| user | symbol | status | value |
----------------------------------
| 101  | A      | 1      | 20    |
| 102  | A      | 1      | 20    |
| 103  | A      | 1      | 20    |
| 101  | A      | 0      | 20    |
| 102  | B      | 1      | 20    |
| 103  | A      | 1      | 20    |
| 101  | A      | 0      | 20    |
| 102  | A      | 1      | 20    |
| 103  | A      | 0      | 20    |
| 101  | B      | 1      | 20    |
| 102  | A      | 0      | 20    |

我想要这样的结果(所有值都变为状态 = 0 的负值)

| user | symbol | differences |
----------------------------------
| 101  | A      | -20         |
| 101  | B      | 20          |
| 102  | A      | 20          |
| 102  | B      | 20          |
| 103  | A      | 20          |

请帮忙，任何帮助将不胜感激!

Answer 1

如果 status = 0，则使用 CASE 表达式将值更改为负值。

查询

 select `user`, `symbol`, 
 sum(case when `status` = 0 then `value` * -1 else `value` end) as differences
 from `your_table_name`
 group by `user`, `symbol`
 order by `user`, `symbol`;

SQL Fiddle demo