主從表的行列轉換輸出(oracle版本)

主表結構:

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

 

輸出效果:

Base_ID  SUBJECT_SCORE 類型1 類型2 類型3 類型4 類型5

1             6                         1        2       3       4       5