一道面试题

[Emperor]

前几天碰到一个面试题，当时没有做出来，贴上来看看：
有一张表:Sale (公司销售记录)，有如下数据：
-------------------------------
year month saleNumber
-------------------------------
2009 1 1000
2009 2 1200
2009 3 1100
2009 4 1150
2010 1 1300
2010 2 1350
2010 3 1250
2010 4 1350
...
--------------------------------

要求写一段sql语句，显示如下结果:
------------------------------------------
year m1 m2 m3 m4
------------------------------------------
2009 1000 1200 1100 1150
2010 1300 1350 1250 1350
...
------------------------------------------
因为一时没想到该怎么做，所以拿上来请教一下哈。

[simon_s52]

4楼的函数做具体题目时可能会有问题的！建议使用3楼答案。

[zujinsheng]

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;

[iqlife]

行转列

[zujinsheng]

简化一下..

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(decode(month,1,saleNumber)) as m1,
       sum(decode(month,2,saleNumber)) as m2,
       sum(decode(month,3,saleNumber)) as m3,
       sum(decode(month,4,saleNumber)) as m4
  from t
 group by year;

[Emperor]

非常感谢您的解答。decode函数是不是只有oracle支持啊？[Quote=引用 3 楼 zujinsheng 的回复:]

简化一下..
SQL code

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, ……
[/Quote]

[Emperor]

这个题目是前几天面试的时候碰到的题目，没说用什么数据库[Quote=引用 4 楼 wl_dmp 的回复:]

不知道你用的是什么数据库
这个是用oracle的函数decode完成的，希望对lz有用
select
year,
max……
[/Quote]

[wl_dmp]

不知道你用的是什么数据库
这个是用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

[zujinsheng]

是的..也可以采用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;

[Quote=引用 6 楼 e_mperor 的回复:]

非常感谢您的解答。decode函数是不是只有oracle支持啊？引用 3 楼 zujinsheng 的回复:

简化一下..
SQL code

with t as
(select 2009 as year,1 as month,1000 as saleNumber from dual UNION ALL
select 2009, 2, 1200 from dual UNION ……
[/Quote]

[cutebear2008]

case when 通用的，干嘛不用呢！ 标准sql