Pivot Table With 3 tables(带有 3 个表的数据透视表)

本文介绍了带有 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

这篇关于带有 3 个表的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！