oracle 行转列，列转行

最新推荐文章于 2025-05-23 21:04:26 发布

HelloBoat

最新推荐文章于 2025-05-23 21:04:26 发布

阅读量1.6k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： oracle

个人随笔，有用你就COPY

本文链接：https://blog.youkuaiyun.com/helloboat/article/details/51160387

oracle 专栏收录该内容

126 篇文章

订阅专栏

本文介绍了Oracle数据库中如何实现行转列和列转行的操作。对于行转列，通过使用decode函数和聚集函数sum配合group by达到效果，例如对各科目分数段的统计。而对于列转行，可以利用SQL的union或insert all into...select语句，后者可能需要创建临时表来完成转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、行转列

主要原理是利用decode函数、聚集函数(sum)，结合group by分组实现的

例：

--查看表是否存在
select * from T_TEST_GRADE T;
--新建测试表
create table T_TEST_GRADE
(
  GRADE_ID  VARCHAR2(10) not null,
  USER_NAME VARCHAR2(20),
  COURSE    VARCHAR2(20),
  SCORE     VARCHAR2(3)
);
--插入测试数据
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('1','james','english','65');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('2','james','basketball','95');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('3','james','math','75');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('4','hardon','english','75');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('5','hardon','basketball','85');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('6','hardon','math','65');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('7','durant','english','76');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('8','durant','basketball','90');
INSERT INTO T_TEST_GRADE(GRADE_ID,USER_NAME,COURSE,SCORE) VALUES ('9','durant','math','75');
--利用decode和group by进行行转列
select t.user_name,
       sum(decode(t.course, 'english', score, null)) as 英语,
       sum(decode(t.course, 'basketball', score, null)) as 篮球,
       sum(decode(t.course, 'math', score, null)) as 数学
  from t_test_grade t
 group by t.user_name
 order by t.user_name;

如果要实现对各门功课的不同分数段进行统计

效果如下：

分数段英语篮球数学

60-80 3 0 3
80-100 0 3 0

相应的sql如下，最终还是行转列：

select t2.分数段,
       sum(decode(t2.course, 'english', COUNTNUM, 0)) as 英语,
       sum(decode(t2.course, 'basketball', COUNTNUM, 0)) as 篮球,
       sum(decode(t2.course, 'math', COUNTNUM, 0)) as 数学
  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 分数段,
               count(t.score) as COUNTNUM
          FROM t_test_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.分数段
 order by t2.分数段;

二、列转行

主要原理是利用SQL里面的union，也可以利用【 insert all into ... select 】来实现，insert all into...select需要额外新建临时表

例：

select * from T_TEST_GRADE2 T;
create table T_TEST_GRADE2
(
  GRADE_ID   NUMBER(10) not null,
  USER_NAME  VARCHAR2(20),
  ENGLISH_SCORE   VARCHAR2(3),
  BASKETBALL_SCORE VARCHAR2(3),
  MATH_SCORE   VARCHAR2(3)
);
INSERT INTO T_TEST_GRADE2(GRADE_ID,USER_NAME,ENGLISH_SCORE,BASKETBALL_SCORE,MATH_SCORE) VALUES ('1','james','65','95','75');
INSERT INTO T_TEST_GRADE2(GRADE_ID,USER_NAME,ENGLISH_SCORE,BASKETBALL_SCORE,MATH_SCORE) VALUES ('2','hardon','75','85','65');
INSERT INTO T_TEST_GRADE2(GRADE_ID,USER_NAME,ENGLISH_SCORE,BASKETBALL_SCORE,MATH_SCORE) VALUES ('3','durant','76','90','75');

select user_name, '英语' COURSE, english_score as SCORE
  from t_test_grade2
union
select user_name, '篮球' COURSE, basketball_score as SCORE
  from t_test_grade2
union
select user_name, '数学' COURSE, math_score as SCORE
  from t_test_grade2
 order by user_name, COURSE;

select * from T_TEST_GRADE3 t;
create table T_TEST_GRADE3  
( 
       USER_NAME VARCHAR2(20),  
       COURSE    VARCHAR2(20),  
       SCORE     VARCHAR2(20)  
);

insert all
into t_test_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', english_score)
into t_test_grade3(USER_NAME,COURSE,SCORE) values(user_name, '篮球', basketball_score)
into t_test_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', math_score)
select user_name, english_score, basketball_score, math_score from t_test_grade2;