主表结构:
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