我试图从三个表生成透视图：
学生
费用
学费（关系表）
桌子：
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 |
+-----------+------------------+-------------+---------+-------+-------+
最佳答案
您可能需要将未知数量的费用转换为列，如果是这种情况，则需要使用准备好的语句来查询：
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
关于mysql - 带有3个表的数据透视表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12986802/