在mysql中实现表的转置

我使用的是mysql,有这样一张表，如图：

这是一个关于统计不同线路不同时段早晚高峰平均车速的表，那么如何把它变成这样的表呢？如图：

也就是常说的“转置”。

实际上，可以用case 和group by来实现，假设这张表的名字是avg_speed_statistics，并且在line_id和period_name两个字段上创建了唯一索引，那么实现语句如下：

select line_id as 线路ID,sum(CASE period_namewhen '早高峰' then avg_speedelse 0END) as 早高峰,sum(CASE period_namewhen '晚高峰' then avg_speedelse 0END) as 晚高峰,sum(CASE period_namewhen '日均' then avg_speedelse 0END) as 日均

 from avg_speed_statisticsGROUP BYline_id

需要注意的是，如果line_id和period_name两个字段在表内不是唯一的，则需要考虑在sum()后除以count()，例如sum(...)/count(distinct line_id)，或者使用其他的分组函数，这个需要根据实际情况考虑。如果简单求平均的话，可以把语句改成这样：​​​​​​​

select line_id as 线路ID,sum(CASE period_namewhen '早高峰' then avg_speedelse 0END)/count(DISTINCT line_id) as 早高峰,sum(CASE period_namewhen '晚高峰' then avg_speedelse 0END)/count(DISTINCT line_id) as 晚高峰,sum(CASE period_namewhen '日均' then avg_speedelse 0END)/count(DISTINCT line_id) as 日均

 from avg_speed_statisticsGROUP BYline_id

这条语句能保证即使line_id和period_name在表里不唯一的情况下，也能得到正确的结果。当然前提是可以简单求平均。

请关注公众号：规划与代码之旅