create or replace view test as select a.*,b.姓名 as 导师姓名 from (select 学生id,tab2.姓名 as 学生姓名,导师id from tab1,tab2 where tab1.学生id=tab2.id) a,tab2 b where a.导师id=b.id;
a表:学生id,导师id b表:id,姓名 create or replace force view view_name as select c.aa 学生id,c.bb 学生姓名,c.cc 导师id b.姓名 导师姓名 from (select a.学生id aa,b.姓名 bb,a.导师id cc from a,b where a.学生id=b.id) c,b;试试看
create table t_id(stu_id number(8), tea_id number(8)) ; create table t_name(id number(8), name varchar2(20)) ;begin for i in 1..20 loop insert into t_id values(i, 20+i); insert into t_name values(i, 'student_' || i); insert into t_name values(i+20, 'teacher_' || i); end loop; end;commit;create or replace view v_s_t_1 as select a.stu_id 学生编号, b.name 学生姓名, a.tea_id 教师编号, c.name 教师姓名 from t_id a, t_name b, t_name c where a.stu_id=b.id and a.tea_id=c.id;select * from v_s_t_1 ; 学生编号 学生姓名 教师编号 教师姓名 ---------- -------------------- ---------- -------------------- 1 student_1 21 teacher_1 2 student_2 22 teacher_2 3 student_3 23 teacher_3 4 student_4 24 teacher_4 5 student_5 25 teacher_5 6 student_6 26 teacher_6 7 student_7 27 teacher_7 8 student_8 28 teacher_8 9 student_9 29 teacher_9 10 student_10 30 teacher_10 11 student_11 31 teacher_11 12 student_12 32 teacher_12 13 student_13 33 teacher_13 14 student_14 34 teacher_14 15 student_15 35 teacher_15 16 student_16 36 teacher_16 17 student_17 37 teacher_17 18 student_18 38 teacher_18 19 student_19 39 teacher_19 20 student_20 40 teacher_20已选择20行。
select a.*,b.姓名 as 导师姓名 from (select 学生id,tab2.姓名 as 学生姓名,导师id from tab1,tab2 where tab1.学生id=tab2.id) a,tab2 b where a.导师id=b.id;
b表:id,姓名
create or replace force view view_name as select c.aa 学生id,c.bb 学生姓名,c.cc 导师id b.姓名 导师姓名 from (select a.学生id aa,b.姓名 bb,a.导师id cc from a,b where a.学生id=b.id) c,b;试试看
create table t_name(id number(8), name varchar2(20)) ;begin
for i in 1..20 loop
insert into t_id values(i, 20+i);
insert into t_name values(i, 'student_' || i);
insert into t_name values(i+20, 'teacher_' || i);
end loop;
end;commit;create or replace view v_s_t_1 as
select a.stu_id 学生编号, b.name 学生姓名, a.tea_id 教师编号, c.name 教师姓名
from t_id a, t_name b, t_name c
where a.stu_id=b.id and a.tea_id=c.id;select * from v_s_t_1 ;
学生编号 学生姓名 教师编号 教师姓名
---------- -------------------- ---------- --------------------
1 student_1 21 teacher_1
2 student_2 22 teacher_2
3 student_3 23 teacher_3
4 student_4 24 teacher_4
5 student_5 25 teacher_5
6 student_6 26 teacher_6
7 student_7 27 teacher_7
8 student_8 28 teacher_8
9 student_9 29 teacher_9
10 student_10 30 teacher_10
11 student_11 31 teacher_11
12 student_12 32 teacher_12
13 student_13 33 teacher_13
14 student_14 34 teacher_14
15 student_15 35 teacher_15
16 student_16 36 teacher_16
17 student_17 37 teacher_17
18 student_18 38 teacher_18
19 student_19 39 teacher_19
20 student_20 40 teacher_20已选择20行。