具有动态旋转的 MySQL 查询

Question

我有一个名为 Grades 的表，如下所示:

  +--------+--------+-----------+
  | userid | itemid | finalgrade|
  +--------+--------+-----------+
  |  1     |   7    |    100    | 
  |  1     |   8    |    89     |    
  |  2     |   7    |    87     |     
  |  2     |   8    |    17     |    
  |  3     |   7    |    87     |   
  |  3     |   8    |    38     |   
  +--------+--------+-----------+

我想创建一个数据透视查询来更改表，如下所示:

  +--------+---+---+
  | userid | 7 | 8 |
  +--------+---+---+
  |  1     |100| 89| 
  |  2     |87 | 17|        
  |  3     |87 | 38|    
  +--------+---+---+

我有一个解决方案，使用像这样定义的itemid:

select *,
truncate (sum(finalgrade*(1-abs(sign(itemid-7)))),0) as 7,
truncate (sum(finalgrade*(1-abs(sign(itemid-8)))),0) as 8,
FROM grades
group by userid"

问题是我需要相同的查询，但未定义 itemid，动态创建列。

Answer 1

我认为您想要这样的查询:

SELECT userid,
  MAX(CASE WHEN itemid=7 THEN finalgrade END) as c7,
  MAX(CASE WHEN itemid=8 THEN finalgrade END) as c8,
FROM grades
GROUP BY userid

但是您希望它具有动态值，然后您可以使用如下准备好的语句:

SELECT    
  CONCAT(    
    'SELECT userid,',    
    GROUP_CONCAT(CONCAT('MAX(CASE WHEN itemid=', itemid, ' THEN finalgrade END) AS c', itemid)),    
    ' FROM tablename GROUP BY userid')    
FROM (SELECT DISTINCT itemid FROM tablename) s INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

您可以使用 SUM 而不是 MAX，具体取决于数据的结构。