oracle 在oracle 10g中加入三个表

Joining three tables in oracle 10g

提问by Razia

I have 3 tables, College, Student and Result

我有 3 个表，College、Student 和 Result

create table college
(
clg_id int,
clg_name varchar2()50,
insert into college values(1,'GIFT');
insert into college values(2,'GITA');
insert into college values(3,'MIT');

create table student
(
clg_id int,
regno int,
sname varchar2(50)
)

insert into student values(1,10,sahar);
insert into student values(2,11,raj);
insert into student values(3,12,Payal);
insert into student values(3,13,Monalisha);
insert into student values(2,14,mary);

create table Result
(
clg_id int,
sname varchar2(50),
clg_name varchar2(50),
regno int,
dt date,
result varchar2(30)
)

insert into Result values(1,'sahar',10,'GIFT',20-02-1990,'A+');
insert into Result values(2,'raj',11,'GITA',21-02-1991,''B+);
insert into Result values(3,'monalisha',13,'MIT',22-09-2005,'A++');
insert into Result values(3,'payal',14,'MIT',22-09-2005,'C');

I want to give a particular dtand clg_idfrom the browser and to display the result, student name, college name and the regnoon which date the result has been stored.

我想从浏览器中给出一个特定的dt和clg_id并显示result学生姓名、大学名称以及regno结果的存储日期。

For example: I will enter clg_id = 3and dt = 22-09-2005then it should display:

例如：我将输入clg_id = 3，dt = 22-09-2005然后它应该显示：

 clg_name   sname      regno    result      
 MIT       monalisha   13        A++     
 MIT       payal        14       C      

I tried a lot... one of my attempts is

我尝试了很多......我的尝试之一是

SELECT college.clg_name,student.sname,
student.regno result.result  FROM college,student,result
WHERE college.clg_id=student.clg_id=result.date;

but it's wrong... Please help.

但这是错误的...请帮忙。

回答by Ben

Okay, in order to do this you don't actually need to join on 3 tables. I can be done on 2 as follows. Please note I've used the explicit as opposed to implicit join syntax. This has been around for a few decades now and should really be used.

好的，为了做到这一点，您实际上并不需要加入 3 个表。我可以按如下方式在 2 上完成。请注意，我使用了显式而不是隐式连接语法。这已经存在了几十年，应该真正被使用。

select r.clg_name, s.sname, r.regno, r.result
  from result r
  join student s
    on r.regno = s.regno
 where r.clg_id = 3
   and r.dt = to_date('22-09-2005','dd-mm-yyyy')

I have also created a SQL Fiddleto demonstrate this.

我还创建了一个SQL Fiddle来演示这一点。

It's possible to do this in two joins because you have partially de-normalised your database and are duplicating information, which could result in inconsistencies. In the table resultthere is no need for the columns snameor clg_name, and, if a student can only be registered at one college there is no need for clg_ideither.

可以在两个连接中执行此操作，因为您已经部分地对数据库进行了非规范化并且正在复制信息，这可能会导致不一致。表中result不需要snameorclg_name和，如果学生只能在一所大学注册，则clg_id两者都不需要。

By removing these columns you ensure that the database does not allow data to be entered incorrectly and save yourself a lot of hassle in the long run. A really good example of this is your schema creation, where in the resulttable Payal is regno14, but is regno12 in your studenttable!

通过删除这些列，您可以确保数据库不允许错误地输入数据，并且从长远来看可以避免很多麻烦。一个很好的例子是你的模式创建，在result表中 Payal 是regno14，但regno在你的student表中是12 ！

If you were to normalise your database properly ( and correcting the above mistake ) your query would be as follows. Please note that I've changed some of your naming conventions so that things are slightly less confusing.

如果您要正确规范化您的数据库（并更正上述错误），您的查询将如下所示。请注意，我已经更改了您的一些命名约定，以使事情变得不那么令人困惑。

select c.name, s.name, s.id, r.result
  from colleges c
  join students s
    on c.id = s.college_id
  join results r
    on s.id = r.student_id
 where c.id = 3
   and r.dt =  to_date('22-09-2005','dd-mm-yyyy')

Here is the schema for that query, also in a SQL Fiddle. Please note the changes I've made to your own:

这是该查询的架构，也在SQL Fiddle 中。请注意我对您自己所做的更改：

1. Standardised naming convention.
2. int--> integer.
3. There are multiple studentsin the studenttable so I've pluralised the name ( and the others ).
4. Primary and foreign key constraints - veryimportant to maintain integrity. A result, must belong to a student, who must belong to a college.
5. Please also note that you were inputting dates incorrectly. Neverrely on implicit conversion and alwaysdo it explicitly.
6. Removal of unnecessary columns.
7. I've added a primary key to the resultstable. Though this is a surrogate key, i.e. it bears no relation to the data, a table should always have a primary key. You may want to add a unique constraint on student_id, dtbut I would prefer the option outlined in point 8.
8. I would have thought you would have required yet another table, exams, and have exam_idas a foreign key in results. I haven't added this but it's something to think about.
9. Student name has been increased to the maximum. You just can't tell with names and it's best not to be overly restrictive.

1. 标准化命名约定。
2. int--> integer.
3. 桌子上有多个学生，student所以我把名字（和其他人）复数化了。
4. 主键和外键约束——对于保持完整性非常重要。结果，一定是属于学生的，一定是属于学院的。
5. 另请注意，您输入的日期不正确。永远不要依赖隐式转换，始终显式执行。
6. 删除不必要的列。
7. 我在results表中添加了一个主键。虽然这是一个代理键，即它与数据无关，但表应该始终有一个主键。您可能想要添加唯一约束，student_id, dt但我更喜欢第 8 点中概述的选项。
8. 我原以为你会需要另一个表，exams，并exam_id在results. 我没有添加这个，但这是需要考虑的。
9. 学生姓名已增加到最大值。你只是不能用名字来分辨，最好不要过于严格。

create table colleges
(
    id integer,
    name varchar2(50),
    constraint pk_colleges primary key ( id )
  );

create table students
(
    id integer,
    name varchar2(4000), -- Use the maximum. Names are impossible to predict
    college_id integer,
    constraint pk_students primary key ( id ),
    constraint fk_student_college foreign key ( college_id )
       references colleges ( id )
);

create table Results
(
    id integer,
    student_id integer,
    dt date,
    result varchar2(30),
    constraint pk_results primary key ( id ),
    constraint fx_result_student foreign key ( student_id )
       references students ( id )
);

Further Reading:

进一步阅读：

• A visual representation of joinsfrom Coding Horror
• Join explanationfrom Wikipedia
• Documentation on constraints
• Documentation on create table
• Create table examplesfrom TECH on the Net
• Comparing Dates in Oracle SQL- My own answer about dates in Oracle

• Coding Horror中连接的可视化表示
• 加入维基百科的解释
• 有关约束的文档
• 创建表的文档
• 从网络上的 TECH创建表格示例
• 比较 Oracle SQL 中的日期- 我自己对 Oracle 中日期的回答