下面我们来模拟:
SQL> select * from test;
YEAR MONTH AMOUNT
---------- ---------- ----------
2014 1 1.1
2014 2 1.2
2015 1 2.1
2015 2 2.2
SQL> select year,
2 sum(decode(month,'1',amount,'0')) as m1,
3 sum(decode(month,'2',amount,'0')) as m2
4 from test group by year;
YEAR M1 M2
---------- ---------- ----------
2014 1.1 1.2
2015 2.1 2.2
对于为什么要使用sum()函数有点疑问
SQL> select year,
2 decode(month,'1',amount,'0') as m1,
3 decode(month,'2',amount,'0') as m2
4 from test group by year;
decode(month,'1',amount,'0') as m1,
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression
可以发现是因为使用了group by分组函数才需要sum函数。
二、下面我们来实现列转行:
SQL> select * from test_tb_grade2;
USER_NAME CN_SCORE MATH_SCORE EN_SCORE
--------------------- ---------- ---------- ----------
lisi 87 65 75
xixi 87 65 75
要实现如下的查询效果:
SQL> select * from test_tb_grade2;
USER_NAME CN_SCORE MATH_SCORE EN_SCORE
--------------------- ---------- ---------- ----------
lisi 87 65 75
xixi 87 65 75
1、 利用SQL里面的union:
select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2
order by user_name,COURSE
2、也可以利用【 insert all into ... select 】来实现,首先需要先建一个表TEST_TB_GRADE3:
Sql代码
create table TEST_TB_GRADE3
(
USER_NAME VARCHAR2(20 CHAR),
COURSE VARCHAR2(20 CHAR),
SCORE FLOAT
)
再执行下面的sql:
Sql代码
insert all
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)
select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;
commit;
别忘记commit操作,然后再查询TEST_TB_GRADE3,发现表中的数据就是列转成行
Sql代码
create table TEST_TB_GRADE3
(
USER_NAME VARCHAR2(20 CHAR),
COURSE VARCHAR2(20 CHAR),
SCORE FLOAT
)
再执行下面的sql:
Sql代码
insert all
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE)
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE)
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE)
select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;
commit;
别忘记commit操作,然后再查询TEST_TB_GRADE3,发现表中的数据就是列转成行
文章借鉴:http://www.2cto.com/database/201108/100792.html