oracle 行转列和列转行
来源:互联网 发布:单片机驱动步进电机 编辑:程序博客网 时间:2024/05/16 07:48
一、行转列
1.1、初始测试数据
表结构:TEST_TB_GRADE,初始数据如下图:
1.2、 如果需要实现如下的查询效果图:
这就是最常见的行转列,主要原理是利用decode函数、聚集函数(sum),结合group by分组实现的,具体的sql如下:
Sql代码:
select t.user_name, sum(decode(t.course, '语文', score,null)) as CHINESE, sum(decode(t.course, '数学', score,null)) as MATH, sum(decode(t.course, '英语', score,null)) as ENGLISHfrom test_tb_grade tgroup by t.user_nameorder by t.user_name
case..when
select t.user_name, sum(case when subject='语文' then score else 0 end) as CHINESE, sum(case when subject='数学' then score else 0 end) as MATH, sum(case when subject='英语' then score else 0 end) as ENGLISHfrom student_scoregroup by t.user_nameorder by t.user_name;
1.3、延伸
如果要实现对各门功课的不同分数段进行统计,效果图如下:
具体的实现sql如下:
Sql代码:
select t2.SCORE_GP, sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE, sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH, sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISHfrom ( 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' endorder by t.course ) t2group by t2.SCORE_GPorder by t2.SCORE_GP
二、列转行
1.1、初始测试数据
表结构: TEST_TB_GRADE2 ,初始数据如下图:1.2、 如果需要实现如下的查询效果图:
这就是最常见的列转行,主要原理是利用SQL里面的union,具体的sql语句如下:
Sql代码:
select user_name, 'CN_SCORE' COURSE , CN_SCORE as SCORE from test_tb_grade2 unionselect user_name, 'MATH_SCORE' COURSE, MATH_SCORE as SCORE from test_tb_grade2 unionselect user_name, 'EN_SCORE' COURSE, EN_SCORE as SCORE from test_tb_grade2 order by user_name,COURSE
也可以利用【insert all into ... select】来实现,首先需要先建一个表TEST_TB_GRADE3:
Sql代码:1
create
table
TEST_TB_GRADE3
2
(
3
USER_NAME VARCHAR2(20
CHAR
),
4
COURSE VARCHAR2(20
CHAR
),
5
SCORE
FLOAT
6
)
再执行下面的sql:
Sql代码:
1
insert
all
2
into
test_tb_grade3(USER_NAME,COURSE,SCORE)
values
(user_name,
'语文'
, CN_SCORE)
3
into
test_tb_grade3(USER_NAME,COURSE,SCORE)
values
(user_name,
'数学'
, MATH_SCORE)
4
into
test_tb_grade3(USER_NAME,COURSE,SCORE)
values
(user_name,
'英语'
, EN_SCORE)
5
select
user_name, CN_SCORE, MATH_SCORE, EN_SCORE
from
test_tb_grade2;
6
commit
;
别忘记commit操作,然后再查询TEST_TB_GRADE3,发现表中的数据就是列转成行了。
0 0
- oracle行转列 和 列转行
- oracle 行转列和列转行
- Oracle 行转列和列转行
- Oracle行转列和列转行
- Oracle行转列和列转行
- Oracle行转列和列转行
- Oracle行转列和列转行
- oracle行转列和列转行
- oracle行转列和列转行
- Oracle行转列和列转行
- ORACLE 列转行和行转列的SQL
- Oracle的行转列和列转行
- 行转列.列转行 oracle
- oracle 行转列,列转行
- 【ORACLE】行转列,列转行
- oracle 行转列 列转行
- oracle 行转列,列转行
- oracle 行转列 列转行
- swing 界面编程
- 6410 selected device is not a touchscreen I understand
- HiveOptimization--Reduce进程缓慢问题
- 舞魔金星[读书笔记]
- ftk学习记(多窗口篇)
- oracle 行转列和列转行
- Android.os.NetworkOnMainThreadException异常
- 好用流程图工具
- Unity手游之路<一>C#版本Protobuf
- [Wikioi 1020]孪生蜘蛛
- MFC文件读写
- JAVA中使用FTPClient上传
- 【Python】Python处理Json文件
- 【14】最近公共祖先问题