目录结构如下: 行转列 列转行 [一]、行转列 1.1、初始测试数据 表结构:TEST_TB_GRADE Sql代码 create table TEST_TB_GRADE ( ID NUMBER(10) not null, USER_NAME VARCHAR2(20 CHAR), COURSE VARCHAR2(20 CHAR), SCORE FLOAT ) 初始数据如下图:
1.2、 如果需要实现如下的查询效果图:
这就是最常见的行转列,主要原理是利用decode函数、聚集函数(sum),结合group by分组实现的,具体的sql如下: Sql代码 select t.user_name, sum(decode(t.course,'语文',score,null)) as CHINESE,'数学',null)) as MATH,'英语',null)) as ENGLISH from test_tb_grade t group by t.user_name order by t.user_name 1.3、延伸 如果要实现对各门功课的不同分数段进行统计,效果图如下:
具体的实现sql如下: Sql代码 select t2.SCORE_GP, sum(decode(t2.course,COUNTNUM,null)) as ENGLISH from ( select t.course, case when t.score <60 then '00-60' when t.score >=60 and t.score <80 then '60-80' when t.score >=80 then '80-100' end as SCORE_GP, count(t.score) as COUNTNUM FROM test_tb_grade t group by t.course, case when t.score <60 then '00-60' when t.score >=60 and t.score <80 then '60-80' when t.score >=80 then '80-100' end order by t.course ) t2 group by t2.SCORE_GP order by t2.SCORE_GP [二]、列转行 1.1、初始测试数据 表结构:TEST_TB_GRADE2 Sql代码 create table TEST_TB_GRADE2 ( ID NUMBER(10) not null, CN_SCORE FLOAT, MATH_SCORE FLOAT, EN_SCORE FLOAT ) 初始数据如下图:
1.2、 如果需要实现如下的查询效果图:
这就是最常见的列转行,主要原理是利用SQL里面的union,具体的sql语句如下: Sql代码 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 也可以利用【 insert all into ... select 】来实现,首先需要先建一个表TEST_TB_GRADE3: Sql代码 create table TEST_TB_GRADE3 ( USER_NAME 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,MATH_SCORE) into test_tb_grade3(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 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|