一道面试题

Emperor 2011-12-19 11:40:49
前几天碰到一个面试题,当时没有做出来,贴上来看看:
有一张表: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
...
------------------------------------------
因为一时没想到该怎么做,所以拿上来请教一下哈。
...全文
182 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
cutebear2008 2011-12-21
  • 打赏
  • 举报
回复
case when 通用的,干嘛不用呢! 标准sql
zujinsheng 2011-12-21
  • 打赏
  • 举报
回复
是的..也可以采用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]
wl_dmp 2011-12-20
  • 打赏
  • 举报
回复
不知道你用的是什么数据库
这个是用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
Emperor 2011-12-20
  • 打赏
  • 举报
回复
这个题目是前几天面试的时候碰到的题目,没说用什么数据库[Quote=引用 4 楼 wl_dmp 的回复:]

不知道你用的是什么数据库
这个是用oracle的函数decode完成的,希望对lz有用
select
year,
max……
[/Quote]
Emperor 2011-12-20
  • 打赏
  • 举报
回复
非常感谢您的解答。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]
zujinsheng 2011-12-20
  • 打赏
  • 举报
回复
简化一下..

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;
iqlife 2011-12-20
  • 打赏
  • 举报
回复
行转列
zujinsheng 2011-12-20
  • 打赏
  • 举报
回复

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;

simon_s52 2011-12-20
  • 打赏
  • 举报
回复
4楼的函数做具体题目时可能会有问题的!建议使用3楼答案。

17,382

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧