如何在MySql中旋转查询 [英] How to pivot a query in MySql

问题描述

我有下表:

Year, Name, Revenue, Qty 

我想要一个带有

名称，Revenue2012，Qty2012，Revenue2013，Qty2013

Name, Revenue2012, Qty2012, Revenue2013, Qty2013

在Sql for MySql中如何做?

How to do in Sql for MySql ?

谢谢

推荐答案

MySQL没有PIVOT函数，但是您可以使用带有CASE表达式的聚合函数来获得结果:

MySQL doesn't have a PIVOT function but you can get the result using an aggregate function with a CASE expression:

select name,
  sum(case when year = 2012 then revenue else 0 end) revenue2012,
  sum(case when year = 2012 then qty else 0 end) qty2012,
  sum(case when year = 2013 then revenue else 0 end) revenue2013,
  sum(case when year = 2013 then qty else 0 end) qty2013
from yourtable
group by name

这篇关于如何在MySql中旋转查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！