mysql> select * from test;
+----+------+-------+---------+-------+
| id | name | level | dangwei | value |
+----+------+-------+---------+-------+
| 1 | A | ONE | 件 | 5 |
| 2 | B | TWO | 支 | 10 |
| 3 | A | TWO | 件 | 3 |
| 4 | A | ONE | 支 | 15 |
| 5 | B | ONE | 件 | 2 |
+----+------+-------+---------+-------+
5 rows in set (0.00 sec)mysql> select name,level,SUM(value*if(dangwei='件',17,1)) as `合计`
-> from test
-> Group by name,level;
+------+-------+------+
| name | level | 合计 |
+------+-------+------+
| A | ONE | 100 |
| A | TWO | 51 |
| B | ONE | 34 |
| B | TWO | 10 |
+------+-------+------+
4 rows in set (0.09 sec)
A和B参数不一样时怎么办,A 1件15,B 1件17,他们的对应关系保存在另外一个表中
+----+------+-------+---------+-------+
| id | name | level | dangwei | value |
+----+------+-------+---------+-------+
| 1 | A | ONE | 件 | 5 |
| 2 | B | TWO | 支 | 10 |
| 3 | A | TWO | 件 | 3 |
| 4 | A | ONE | 支 | 15 |
| 5 | B | ONE | 件 | 2 |
+----+------+-------+---------+-------+
5 rows in set (0.00 sec)mysql> select name,level,SUM(value*if(dangwei='件',17,1)) as `合计`
-> from test
-> Group by name,level;
+------+-------+------+
| name | level | 合计 |
+------+-------+------+
| A | ONE | 100 |
| A | TWO | 51 |
| B | ONE | 34 |
| B | TWO | 10 |
+------+-------+------+
4 rows in set (0.09 sec)
A和B参数不一样时怎么办,A 1件15,B 1件17,他们的对应关系保存在另外一个表中
from test inner join 另外一个表 on test.name=另外一个表.xxxx
Group by name,level;