日期:2014-05-17 浏览次数:20947 次
with t as (select 2009 as year,1 as month,1000 as saleNumber from dual UNION ALL select 2009, 2, 1200 from dual UNION ALL select 2009, 3, 1100 from dual UNION ALL select 2009, 4, 1150 from dual UNION ALL select 2010, 1, 1300 from dual UNION ALL select 2010, 2, 1350 from dual UNION ALL select 2010, 3, 1250 from dual UNION ALL select 2010, 4, 1350 from dual ) select year,sum(m1) m1,sum(m2) m2,sum(m3) m3,sum(m4) m4 from (select year, decode(month,1,saleNumber) as m1, decode(month,2,saleNumber) as m2, decode(month,3,saleNumber) as m3, decode(month,4,saleNumber) as m4 from t) group by year;
------解决方案--------------------
行转列
------解决方案--------------------
不知道你用的是什么数据库
这个是用oracle的函数decode完成的,希望对lz有用
select
year,
max(decode(month,'1',salenumber)) as m1 ,
max(decode(month,'2',salenumber)) as m2,
max(decode(month,'3',salenumber)) as m3 ,
max(decode(month,'4',salenumber)) as m4
from test
GROUP BY year
------解决方案--------------------
4楼的函数做具体题目时可能会有问题的!建议使用3楼答案。
------解决方案--------------------
case when 通用的,干嘛不用呢! 标准sql
------解决方案--------------------
是的..也可以采用8L 说的通用 case when
with t as (select 2009 as year,1 as month,1000 as saleNumber from dual UNION ALL select 2009, 2, 1200 from dual UNION ALL select 2009, 3, 1100 from dual UNION ALL select 2009, 4, 1150 from dual UNION ALL select 2010, 1, 1300 from dual UNION ALL select 2010, 2, 1350 from dual UNION ALL select 2010, 3, 1250 from dual UNION ALL select 2010, 4, 1350 from dual ) select year, sum(case month when 1 then saleNumber end) as m1, sum(case month when 2 then saleNumber end) as m2, sum(case month when 3 then saleNumber end) as m3, sum(case month when 4 then saleNumber end) as m4 from t group by year;