mysql - 如何在mysql中透视一个有n行的表？

Question

如果我有 1000 行，我想将每一行变成列，并且行可以是任何内容。
我如何在 mysql 中做到这一点？

Answer 1

这是按工作日预订房间的 MySQL 数据透视表查询:

SELECT slot 
  , max(if(day=1, concat(subject,' ',room), '')) as day1 
  , max(if(day=2, concat(subject,' ',room), '')) as day2 
  , max(if(day=3, concat(subject,' ',room), '')) as day3 
  , max(if(day=4, concat(subject,' ',room), '')) as day4 
  , max(if(day=5, concat(subject,' ',room), '')) as day5 
from schedule 
group by slot 

MAX(...) 在条目和空白之间做出决定(如果存在则条目获胜)，而 group by 将所有内容排列在同一行上。友情提醒:如果同一天同一时间存在多个条目，您只会看到按字母顺序“较大”的条目。

要查看每个时段每天安排了多少类(class)(以检查是否存在冲突)，请尝试:

SELECT slot 
  , sum(if(day=1,1,0)) as day1 
  , sum(if(day=2,1,0)) as day2 
  , sum(if(day=3,1,0)) as day3 
  , sum(if(day=4,1,0)) as day4 
  , sum(if(day=5,1,0)) as day5 
from schedule 
group by slot 

在此处查看更多解决方案:MySQL pivot table