mysql - 带 3 个表的数据透视表

Question

我正在尝试从三个表生成数据透视图:

1. 学生
2. 费用
3. stud_fee(关系表)

表格:

Students Table
+----+-----------+-----------+----------------+----------------+-------+
| id | school_id | last_name | first_name     | middle_initial | yrlvl |
+----+-----------+-----------+----------------+----------------+-------+
|  1 | 2080295   | Doe       | John           | A              |     3 |
|  2 | 0239129   | Rizal     | Jose           | M              |     4 |
|  3 | 1231238   | Santos    | Jane           | M              |     2 |
+----+-----------+-----------+----------------+----------------+-------+

Fee table
+----+--------------------+------------+
| id | fee_name           | fee_amount |
+----+--------------------+------------+
|  1 | Registration Fee   |        100 |
|  2 | News Letter        |        100 |
|  3 | T-Shirt            |        250 |
|  4 | Party              |        500 |
+----+--------------------+------------+

stud_fee table
+----+------------+-----+
| id | stud_id | fee_id |
+----+---------+--------+
|  1 |       1 |      1 |
|  2 |       1 |      2 |
|  3 |       1 |      3 |
|  4 |       2 |      1 |
|  5 |       3 |      1 |
|  6 |       3 |      4 |
+----+---------+--------+

我想将费用作为列，将学生作为行。我想让它显示为:

+-----------+------------------+-------------+---------+-------+-------+
| school_id | Registration Fee | News Letter | T-Shirt | Party | Total |
+-----------+------------------+-------------+---------+-------+-------+
| 2080295   |              100 |         100 |     250 |       |   450 |
| 0239129   |              100 |             |         |       |   100 |
| 1231238   |              100 |             |         |   500 |   600 |
+-----------+------------------+-------------+---------+-------+-------+

Answer 1

看起来您可能有未知数量的费用要转换成列，如果是这种情况，那么您将需要使用准备好的语句来查询:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when f.fee_name = ''',
      f.fee_name,
      ''' then f.fee_amount else 0 end) AS `',
      f.fee_name, '`'
    )
  ) INTO @sql
FROM fee f;

SET @sql = CONCAT('SELECT s.school_id, ', @sql, '
                    , sum(f.fee_amount) as Total
                  FROM students s
                  LEFT JOIN stud_fee sf
                    on s.id = sf.stud_id
                  LEFT JOIN fee f
                    on sf.fee_id = f.id
                   GROUP BY s.school_id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见 SQL Fiddle with Demo