我正在尝试从三个表生成数据透视图：
表格：
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;
