一道面试题

Question

前几天碰到一个面试题，当时没有做出来，贴上来看看：
有一张表: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
...
------------------------------------------
因为一时没想到该怎么做，所以拿上来请教一下哈。

Answer 1

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;

Answer 2

简化一下..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;

Answer 3

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

Answer 4

非常感谢您的解答。decode函数是不是只有oracle支持啊？

Answer 5

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

Answer 6

是的..也可以采用8L 说的通用 case whenwith 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;