mysql根据条件将行转换为列

Question

是否可以通过这种方式将 mysql 行转换为列？

假设我有这样一张 table

+-----+--------------+---------+--------------+-------+
| id  | std_no       | exam_id | subject_code | score |
+-----+--------------+---------+--------------+-------+
|   1 | 1000         |       1 |          101 |    70 |
|   2 | 1000         |       1 |          102 |    75 |
|   3 | 1000         |       1 |          121 |    75 |
|   4 | 1000         |       1 |          231 |    69 |
|   7 | 1001         |       1 |          101 |    80 |
|   8 | 1001         |       1 |          102 |    70 |
|   9 | 1001         |       1 |          121 |    90 |
|  10 | 1001         |       1 |          231 |    80 |
| 127 | 1000         |       2 |          101 |    61 |
| 128 | 1000         |       2 |          102 |    85 |
| 129 | 1000         |       2 |          121 |    50 |
| 130 | 1000         |       2 |          231 |    54 |
| 133 | 1001         |       2 |          101 |    63 |
| 134 | 1001         |       2 |          102 |    14 |
| 135 | 1001         |       2 |          121 |    90 |
| 136 | 1001         |       2 |          231 |    25 |
+-----+--------------+---------+--------------+-------+

我需要根据上面的内容创建一个新表，如下所示:

+-----+----------------+-------------+-------+-------+
| id  | std_no         |subject_code | exam1 | exam2 |         
+-----+----------------+-------------+-------+-------+
| 1   | 1000           | 101         | 70    | 61    |
| 2   | 1000           | 102         | 75    | 85    |
| 3   | 1000           | 121         | 75    | 50    |
| 4   | 1000           | 231         | 69    | 54    |
| 5   | 1001           | 101         | 80    | 63    |
| 6   | 1001           | 102         | 70    | 14    |
| 7   | 1001           | 121         | 90    | 90    |
| 8   | 1001           | 231         | 80    | 25    |
+-----+----------------+-------------+-------+-------+

通过第一个表，获取std_no、subject_code 和分数。但是我需要把分数e inside table2 under exam1 if table1.exam_id=1 and in table2 under exam2 if table1.exam_id=2

用 mysql 可以吗？

Answer 1

对于动态exam_id，您需要创建一个动态查询来生成数据透视表，如下所示

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when exam_id = ''',
      exam_id,
      ''' then score end) AS ',
      concat('exam',exam_id)
    )
  ) into @sql
from exam;

set @sql = concat('select std_no,subject_code, ', @sql, ' from exam 
                  group by std_no,subject_code
                  order by std_no,subject_code
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

在预期的结果集中，您还有一些 id 递增顺序，要获得该顺序，您可以更改上述查询以使用用户定义的变量来实现相同的结果

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when exam_id = ''',
      exam_id,
      ''' then score end) AS ',
      concat('exam',exam_id)
    )
  ) into @sql
from exam;

set @sql = concat('select @rn:=@rn+1 as id,std_no,subject_code, ', @sql, ' from exam,(select @rn:=0)x
                  group by std_no,subject_code
                  order by std_no,subject_code
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

DEMO