SQL 使用多个表创建视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22489505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CREATE VIEW Using multiple tables
提问by Gideon Sassoon
Hi trying to create a view in oracle. However I'm getting errors in my code which I can't work out how to solve. Currently I'm trying to create a view which shows Managers, their first and last name the clinic they are assigned too The PK of the clinic and the FK of the address of the clinic All addresses are stored in that table (yes I know it's not standard but it's how I am choosing to do it) So I also want to show the address details of the clinic which they work at
嗨，试图在 oracle 中创建一个视图。但是，我的代码中出现错误，我不知道如何解决。目前我正在尝试创建一个视图，显示经理，他们的名字和姓氏，他们也被分配到诊所 诊所的 PK 和诊所地址的 FK 所有地址都存储在该表中（是的，我知道它是不是标准的，但这是我选择的方式）所以我还想显示他们工作的诊所的地址详细信息
Of course this include two WHERE statements which I am unsure how to implement The first is where STAFFJOBNAME "MANAGER"
当然，这包括我不确定如何实现的两个 WHERE 语句 第一个是 STAFFJOBNAME "MANAGER"
and the second is where the ADDRESSNO matches on both tables
第二个是 ADDRESSNO 在两个表上匹配的地方
CREATE VIEW MANAGER AS
SELECT STAFF.staffno,STAFF.staffFirstName,STAFF.staffLastName,CLINIC.clinicNo, CLINIC.addressNo
FROM STAFF,CLINIC
WHERE addressNo =
(
SELECT addressNo, addressStreet, addressCity, addressCounty, addressPostcode, addressTelephone,
FROM ADDRESS,
INNER JOIN CLINIC,
ON ADDRESS.addressNo = CLINIC.addressNo
) AND STAFF.staffJobName = 'MANAGER';
I also have this version. So I'm not sure which is more closer to the correct one.
我也有这个版本。所以我不确定哪个更接近正确的。
CREATE VIEW MANAGER
(
AS
SELECT STAFF.staffno,STAFF.staffFirstName,STAFF.staffLastName,CLINIC.clinicNo, CLINIC.addressNo, ADDRESS.addressNo, ADDRESS.addressStreet, ADDRESS.addressCity, ADDRESS.addressCounty, ADDRESS.addressPostcode, ADDRESS.addressTelephone,
FROM ADDRESS, STAFF,
INNER JOIN CLINIC,
ON ADDRESS.addressNo = CLINIC.addressNo
);
回答by Gideon Sassoon
CREATE VIEW MANAGERANDCLINIC AS
SELECT STAFF.staffno,STAFF.staffFirstName,STAFF.staffLastName,CLINIC.clinicNo, ADDRESS.addressStreet, ADDRESS.addressCity, ADDRESS.addressCounty, ADDRESS.addressPostcode, ADDRESS.addressTelephone
FROM STAFF,CLINIC, ADDRESS
WHERE (CLINIC.CLINICMANAGERNO = STAFF.STAFFNO) AND
(CLINIC.ADDRESSNO = ADDRESS.ADDRESSNO)
ORDER BY CLINIC.CLINICNO;
Eventually found the answer to my own question
最终找到了我自己问题的答案
回答by Kerri Williams
You are getting too many values error for your first view because in your inner query you are returning many columns but in the where clause you have only one column.
第一个视图的值错误太多，因为在内部查询中返回了很多列，但在 where 子句中只有一列。
Also in your second code please alter like below
同样在您的第二个代码中，请更改如下
CREATE VIEW MANAGER
AS
SELECT STAFF.staffno,STAFF.staffFirstName,STAFF.staffLastName,CLINIC.clinicNo, CLINIC.addressNo, ADDRESS.addressNo, ADDRESS.addressStreet, ADDRESS.addressCity, ADDRESS.addressCounty, ADDRESS.addressPostcode, ADDRESS.addressTelephone,
FROM ADDRESS, STAFF
INNER JOIN CLINIC
ON ADDRESS.addressNo = CLINIC.addressNo;
