MySQL - 具有计算关系的 PHP SELECT 表

Question

我有这张表:

+------------+-----------+--------+-------+------------+
|  username  | exam_code | module | value | percentage |
+------------+-----------+--------+-------+------------+
|    aaaaa   |     x     |    1   |   60  |      -     |
|    aaaaa   |     x     |    2   |   80  |      -     |
|    aaaaa   |     x     |    3   |   70  |      -     |
|    bbbbb   |     x     |    1   |   60  |      -     |
|    bbbbb   |     x     |    2   |   70  |      -     |
|    bbbbb   |     x     |    3   |   90  |      -     |
+------------+-----------+--------+-------+------------+

我想输入我自己(使用 php POST)，模块的百分比。
例如，如果我将 module 1 = 50%、module 2 = 35%、module 3 = 15%
(总数将是 100)

它将把之前的表格变成:

+------------+-----------+--------+-------+------------+
|  username  | exam_code | module | value | percentage |
+------------+-----------+--------+-------+------------+
|    aaaaa   |     x     |    1   |   60  |     50     |
|    aaaaa   |     x     |    2   |   80  |     35     |
|    aaaaa   |     x     |    3   |   70  |     15     |
|    bbbbb   |     x     |    1   |   70  |     50     |
|    bbbbb   |     x     |    2   |   70  |     35     |
|    bbbbb   |     x     |    3   |   90  |     15     |
+------------+-----------+--------+-------+------------+

直到这一行，我可以用 PHP 来做到这一点

我想问的是，如何将这个表(输入百分比字段后)变成这个

+------------+----------+----------+----------+
|  username  | module 1 | module 2 | module 3 |
+------------+----------+----------+----------+
|    aaaaa   |     30   |    28    |   10.5   |
|    bbbbb   |     35   |    24.5  |   13.5   |
+------------+----------+----------+----------+

(解释)

+------------+----------+----------+----------+
|  username  | module 1 | module 2 | module 3 |
+------------+----------+----------+----------+
|    aaaaa   | (60x50%) | (80x35%) | (70x15%) |
|    bbbbb   | (70x50%) | (70x35%) | (90x15%) |
+------------+----------+----------+----------+

如何SELECT(不创建表)得到这个表，只使用MySQL语法？

Answer 1

像这样的东西应该可以做到。

SELECT username, 
  SUM(CASE WHEN module = 1 THEN value*percentage/100 END) as module1,
  SUM(CASE WHEN module = 2 THEN value*percentage/100 END) as module2,
  SUM(CASE WHEN module = 3 THEN value*percentage/100 END) as module3
FROM tablename
GROUP BY username

SQL Fiddle link