mysql - 转置 MySQL 查询的结果

Question

我想将 MySQL 查询的结果从每行键 => 值结果集转置到一个结果集，其中键是列标题，值是该列的行条目。

即给定以下数据

|------------------+-------------|
|  CLASS_LESSON    | ATTENDANTS  |
|------------------+-------------|
|  class1art       | 1           |
|  class1history   | 1           |
|  class2geography | 2           |
|------------------+-------------|

我想把它变成

|------------+---------------+------------------|
|  class1art | class1history | class2geography  |
|------------+---------------+------------------|
|  1         | 1             | 2                |
|------------+---------------+------------------|

假设类(class)/类(class)对是动态的；它们可以随时添加或删除。我不想按照 typical 'pivot table' sql 中的建议显式调用它们解决方案。

select 
  MAX(CASE WHEN class_lesson = 'class1art' THEN attendants ELSE 0 END) AS class1art,
  MAX(CASE WHEN class_lesson = 'class1history' THEN attendants ELSE 0 END) AS class1history,
  MAX(CASE WHEN class_lesson = 'class2geography' THEN attendants ELSE 0 END) AS class2geography,
  MAX(CASE WHEN class_lesson = 'class7art' THEN attendants ELSE 0 END) AS class7art,
  MAX(CASE WHEN class_lesson = 'class7history' THEN attendants ELSE 0 END) AS class7history

from
    (select 
        group_concat(distinct class, lesson) as class_lesson,
            count(*) as attendants
    from
        TableName
    group by class , lesson
    ) a

这是一个 SQLFiddle带有示例数据的环境。这可能不使用存储过程吗？

Answer 1

试试这个

SELECT  
MAX(CASE WHEN t.CLASS_LESSON = 'class1art' THEN t.ATTENDANTS ELSE NULL END) AS class1art,
MAX(CASE WHEN t.CLASS_LESSON = 'class1history' THEN t.ATTENDANTS ELSE NULL END) AS class1history,
MAX(CASE WHEN t.CLASS_LESSON = 'class2geography' THEN t.ATTENDANTS ELSE NULL END) AS class2geography
FROM 
(
 select 
 group_concat(distinct class, lesson) as class_lesson, count(*) as attendants

 from 
 TableName

 group by 
 class, lesson
) as t

FIDDLE DEMO