带有 3 个表的数据透视表

I'm trying to generate a pivot view from three tables:

1. students
2. fees
3. stud_fee(relation table)

The tables:

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 |
+----+---------+--------+

I would like to make the fee as the columns and students as the rows. I would like to make it display as:

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

解决方案

It looks like you might have an unknown number of fees that you want to turn into columns, if that is the case then you will want to use prepared statements to query this:

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;

See SQL Fiddle with Demo