九乡新闻网股票金字塔买入法:一道sql面试题的多种解答
来源：百度文库 编辑：九乡新闻网 时间：2024/04/27 15:53:29
一道sql面试题的多种解答
2008-11-27 10:49:06
标签：sql 面试 oracle [推送到技术圈]
版权声明：原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://mouren.blog.51cto.com/266465/115634
用一条sql语句实现下面结果：
怎么把这样一个表：
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
以下答案，可能表名不同。
****************
我的答案1：
SQL> select year,
max(decode(month,1,amount)) as m1,
max(decode(month,2,amount)) as m2,
max(decode(month,3,amount)) as m3,
max(decode(month,4,amount)) as m4
from mrtest
group by year;
***********
我的答案2：
SQL> select a.year year,a.amount m1,b.amount m2,c.amount m3,d.amount m4 from
mrtest a,mrtest b,mrtest c,mrtest d
where a.month=1 and b.month=2 and c.month=3 and d.month=4 and a.year=b.year
and b.year=c.year and c.year=d.year;
*****************
其它答案3：
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year
*****************************
答案5：
select year,
sum(case when month = '1' then amount else '0' end) as m1,
sum(case when month = '2' then amount else '0' end) as m2,
sum(case when month = '3' then amount else '0' end) as m3,
sum(case when month = '4' then amount else '0' end) as m4
from table_name
group by year