主从表的行列转换输出(oracle版本)

最新推荐文章于 2024-06-18 16:10:07 发布

灿爸.

最新推荐文章于 2024-06-18 16:10:07 发布

阅读量833

点赞数

本文链接：https://blog.csdn.net/sharpnessdotnet/article/details/5412926

版权

开发语言专栏收录该内容

60 篇文章 0 订阅

订阅专栏

主表结构:

create table EXAM_CONFIG
(
ID                    CHAR(36) not null,
EXAM_ID               CHAR(36),
PAPER_ID              CHAR(36),
SUBJECT_STYLE_TYPE_ID NUMBER(3),
SUBJECT_SCORE         NUMBER(22,2),
WAREHOUSE_ID          CHAR(36),
STATE                 NUMBER(3)
)

从表结构:

create table EXAM_CONFIG_DIFFICULTY
(
ID CHAR(36) not null,
BASE_ID CHAR(36),
DIFFICULTY_ID CHAR(36),
SUBJECT_COUNT NUMBER(3)
)

主从表行列输出:

select t2.base_id,
       t1.subject_score,
       sum(case
             when t2.difficulty_id = '3b2ee9be-679e-4cdb-ac88-20f44771f075' then
              t2.subject_count
           end) as "类型1",
       sum(case
             when t2.difficulty_id = 'fee7bffb-2fcf-412a-882d-952ac6ce2934' then
              t2.subject_count
           end) as "类型2",
       sum(case
             when t2.difficulty_id = '267f15e4-0b27-4b10-b502-7c66544725a4' then
              t2.subject_count
           end) as "类型3",
       sum(case
             when t2.difficulty_id = '73bb48d8-b413-4798-ac58-6c55261629ed' then
              t2.subject_count
           end) as "类型4",
       sum(case
             when t2.difficulty_id = '32f91b44-7eb8-4ba1-b205-79a69e6f712f' then
              t2.subject_count
           end) as "类型5"
from exam_config t1, exam_config_difficulty t2
where t1.id = t2.base_id
group by t2.base_id, t1.subject_score

或者:

     select t2.base_id,
           t1.subject_score,
           sum(decode(t2.difficulty_id,
                      '3b2ee9be-679e-4cdb-ac88-20f44771f075',
                      t2.subject_count,
                      0)) as "类型1",
           sum(decode(t2.difficulty_id,
                      'fee7bffb-2fcf-412a-882d-952ac6ce2934',
                      t2.subject_count,
                      0)) as "类型2",
           sum(decode(t2.difficulty_id,
                      '267f15e4-0b27-4b10-b502-7c66544725a4',
                      t2.subject_count,
                      0)) as "类型3",
           sum(decode(t2.difficulty_id,
                      '73bb48d8-b413-4798-ac58-6c55261629ed',
                      t2.subject_count,
                      0)) as "类型4",
           sum(decode(t2.difficulty_id,
                      '32f91b44-7eb8-4ba1-b205-79a69e6f712f',
                      t2.subject_count,
                      0)) as "类型5"
      from exam_config t1, exam_config_difficulty t2
     where t1.id = t2.base_id
     group by t2.base_id, t1.subject_score