select b1.名称 as 考试科目 b2.名称 as 考试成绩 b3.名称 as 考生 c.名称 as 考试标题 from a inner join b b1 on a.考试科目=b1.状态id and inner join b b2 on a.考试成绩=b2.状态id and inner join b b3 on a.考生=b3.状态id and inner join c on c.类型id=b1.类型id where 自己添加条件
建这么3张表吧: create table A( calss_id varchar2(10),--考试科目 score varchar2(10),--考试成绩 stu_id varchar2(10),--考生 title varchar2(40) --考试标题 ); create table B( type varchar2(10),--类型id state varchar2(10),--状态id state_name varchar2(10)--名称 ); create table C( type varchar2(10),--类型id type_name varchar2(10)--名称 ); 注:实际查询的时候C表是多余的。sql语句为:select t1.state_name class_name,t2.state_name score,t3.state_name student_name,t.title from A t left join B t1 on t1.type=1 and t.calss_id=t1.state left join B t2 on t2.type=2 and t.score=t2.state left join B t3 on t3.type=3 and t.stu_id=t3.state order by class_name,score,student_name
b2.名称 as 考试成绩
b3.名称 as 考生
c.名称 as 考试标题
from a
inner join b b1 on a.考试科目=b1.状态id and
inner join b b2 on a.考试成绩=b2.状态id and
inner join b b3 on a.考生=b3.状态id and
inner join c on c.类型id=b1.类型id
where 自己添加条件
建这么3张表吧:
create table A(
calss_id varchar2(10),--考试科目
score varchar2(10),--考试成绩
stu_id varchar2(10),--考生
title varchar2(40) --考试标题
);
create table B(
type varchar2(10),--类型id
state varchar2(10),--状态id
state_name varchar2(10)--名称
);
create table C(
type varchar2(10),--类型id
type_name varchar2(10)--名称
);
注:实际查询的时候C表是多余的。sql语句为:select
t1.state_name class_name,t2.state_name score,t3.state_name student_name,t.title
from A t
left join B t1 on t1.type=1 and t.calss_id=t1.state
left join B t2 on t2.type=2 and t.score=t2.state
left join B t3 on t3.type=3 and t.stu_id=t3.state
order by class_name,score,student_name