sql - 不明确的列名“ProductNumber”
问题描述
无法弄清楚为什么我的问题 3 会出现上述错误。
下面是我的代码
/* Question 1 */
CREATE TABLE CUSTOMERS
(
CustomerID INT PRIMARY KEY,
CustFirstName VARCHAR(50) NOT NULL,
CustLastName VARCHAR(50) NOT NULL,
CustStreetAddress VARCHAR(50) NOT NULL,
CustCity VARCHAR(50) NOT NULL,
CustState VARCHAR(26) NOT NULL,
CustZipCode INT NOT NULL,
CustAreaCode INT NOT NULL,
CustPhoneNumber VARCHAR (26) NOT NULL
);
CREATE TABLE EMPLOYEES
(
EmployeeID INT PRIMARY KEY,
EmpFirstName VARCHAR(50) NOT NULL,
EmpLastName VARCHAR(50) NOT NULL,
EmpCity VARCHAR (50) NOT NULL,
EmpState VARCHAR(26) NOT NULL,
EmpZipCode INT NOT NULL,
EmpAreaCode INT NOT NULL,
EmpPhoneNumber VARCHAR(26) NOT NULL,
EmpBirthDate DATE NOT NULL
);
CREATE TABLE ORDERS
(
OrderNumber INT PRIMARY KEY,
OrderDate DATE NOT NULL,
ShipDate DATE NOT NULL,
CustomerID INT NOT NULL,
EmployeeID INT NOT NULL,
FOREIGN KEY(EmployeeID) REFERENCES EMPLOYEES(EmployeeID),
FOREIGN KEY(CustomerID) REFERENCES CUSTOMERS(CustomerID)
);
CREATE TABLE CATEGORIES
(
CategoryID INT PRIMARY KEY,
CategoryDescription VARCHAR(255) NOT NULL
);
CREATE TABLE PRODUCTS
(
ProductNumber INT PRIMARY KEY,
ProductName VARCHAR(50) NOT NULL,
ProductDescription VARCHAR(255) NOT NULL,
RetailPrice INT NOT NULL,
QuantityOnHand INT NOT NULL,
CategoryID INT NOT NULL,
FOREIGN KEY(CategoryID) REFERENCES CATEGORIES (CategoryID)
);
CREATE TABLE ORDER_DETAILS
(
OrderNumber INT NOT NULL,
ProductNumber INT NOT NULL,
QuotedPrice INT NOT NULL,
QuantityOrdered INT NOT NULL,
PRIMARY KEY (OrderNumber, ProductNumber),
FOREIGN KEY (OrderNumber) REFERENCES ORDERS(OrderNumber),
FOREIGN KEY(ProductNumber) REFERENCES PRODUCTS(ProductNumber)
);
CREATE TABLE VENDORS
(
VendorID INT PRIMARY KEY,
VendName VARCHAR(100) NOT NULL,
VendStreetAddress VARCHAR(50) NOT NULL,
VendCity VARCHAR(50) NOT NULL,
VendState VARCHAR(26) NOT NULL,
VendFaxNumber VARCHAR(50) NOT NULL,
VendWebPage VARCHAR(100) NOT NULL,
VendEmailAddress VARCHAR(100) NOT NULL
);
CREATE TABLE PRODUCT_VENDORS
(
ProductNumber INT NOT NULL,
VendorID INT NOT NULL,
WholeSalePrice INT NOT NULL,
DaysToDeliver INT NOT NULL,
PRIMARY KEY(ProductNumber, VendorID),
FOREIGN KEY(ProductNumber) REFERENCES PRODUCTS(ProductNumber),
FOREIGN KEY(VendorID) REFERENCES Vendors(VendorID)
);
/* QUESTION 2 */
SELECT
OrderDate, CustFirstName, CustLastName
FROM
CUSTOMERS C, ORDERS O
WHERE
C.CustomerID = O.OrderNumber;
/* QUESTION 3 */
SELECT
ProductNumber, WholeSalePrice, VendName
FROM
PRODUCTS P, PRODUCT_VENDORS PV, VENDORS V
WHERE
P.PRODUCTNUMBER = PV.ProductNumber AND PV.VendorID = V.VendorID;
我得到了错误
不明确的列名“ProductNumber”
解决方案
因为在您的查询中有两个表的列名,所以ProductNumber
您需要告诉数据库引擎您想要获取哪一列。
另外,使用JOIN
而不是,
逗号CROSS JOIN
,因为Join
比逗号更清楚两个表之间的关系
SELECT PV.ProductNumber, WholeSalePrice, VendName
FROM PRODUCTS P
JOIN PRODUCT_VENDORS PV ON P.PRODUCTNUMBER = PV.ProductNumber
JOIN VENDORS V ON PV.VendorID = V.VendorID;
推荐阅读
- svn - .svn 目录是可访问的
- javascript - mix-blend-mode:差异在 Chrome 和 Firefox 中看起来不同
- r - 如何在 R 中用“map-09”替换“map-09”?(从双引号到单引号)
- react-native - 应用程序在 react-native 中进入后台状态后的间隔处理
- bash - 使用我的 bash 脚本将目录更改为嵌套文件夹内的文件夹时出现问题
- java - Twilio:如何在正文消息上发送链接
- amazon-web-services - SQS队列在一定时间后不为空时报警?
- reactjs - REACTJS:值未定义
- ios - 如何存储 [[string]] 并从核心数据中获取它?
- docker - 为什么“sh -c 命令”中的 -c 在某些情况下是强制性的,而在其他情况下是不允许的?