欢迎来到网际学院,让您的头脑满载而归!

Oracle 数据库测试题 数据库行列转换的实现

发布日期:2017-08-20 10:50:46 作者:管理员 阅读:1583

一个SQL笔试题

Oracle 数据库测试题 数据库行列转换的实现

一个SQL笔试题

表名为table的表内容如下
year month value
2009 1    1.1
2009 2    1.2
2009 3    1.3
2009 4    1.4
2010 1    2.1
2010 2    2.2
2010 3    2.3
2010 4    2.4


要求查询结果为

year 一月   2月   3月   4月 
2009 1.1    1.2   1.3   1.4
2010 2.1    2.2   2.3   2.4


创建测试表test2,并插入测试数据
SQL> create table test2(year2 varchar2(20),month1 varchar2(15),valu number(6,2));
Table created.

SQL> insert into test2 values('2009','1',1.1);
1 row created.

SQL> insert into test2 values('2009','2',1.2);
1 row created.

SQL> insert into test2 values('2009','3',1.3);
1 row created.

SQL> insert into test2 values('2009','4',1.4);
1 row created.

SQL>insert into test2 values('2010','1',2.1);
1 row created.

SQL> insert into test2 values('2010','2',2.2);
1 row created.

SQL> insert into test2 values('2010','3',2.3);
1 row created.

SQL> insert into test2 values('2010','4',2.4);
1 row created.

SQL> select * from test2;

YEAR2               MONTH1               VALU
-------------------- --------------- ----------
2009                1                     1.1
2009                2                     1.2
2009                3                     1.3
2009                4                     1.4
2010                1                     2.1
2010                2                     2.2
2010                3                     2.3
2010                4                     2.4

8 rows selected.


使用DECODE函数实现题目要求
SQL> select year2,sum(decode(month1,'1', valu,null))"M1",
 2              sum(decode(month1,'2',valu,null)) "M2",
 3              sum(decode(month1,'3',valu,null)) "M3",
 4              sum(decode(month1,'4',valu,null)) "M4"
 5 from test2
 6 group by year2;

YEAR2                       M1        M2        M3        M4
-------------------- ---------- ---------- ---------- ----------
2009                       1.1       1.2       1.3       1.4
2010                       2.1       2.2       2.3       2.4

使用CASE表达式实现题目要求
SQL> select year2,sum(case when month1='1' then valu end)"M1",
 2                sum(case when month1='2' then valu end)"M2",
 3                sum(case when month1='3' then valu end)"M3",
 4                sum(case when month1='4' then valu end)"M4"
 5 from test2 group by year2 ;

YEAR2                       M1        M2        M3        M4
-------------------- ---------- ---------- ---------- ----------
2009                       1.1       1.2       1.3       1.4
2010                       2.1       2.2       2.3       2.4


Copyright oneie ©2014-2017 All Rights Reserved. 所有资料来源于互联网对相关版权责任概不负责。如发现侵犯了您的版权请与我们联系 QQ:86662817。 网际学院 版权所有 京ICP备14031243号-3
免责声明  商务合作及投稿请联系 QQ:86662817