您好我在下面的查询中有错误PLZ可以帮助我 [英] hi am having errors in the following query plz can u help me

问题描述

CREATE TABLE Supplier(
supplierNo    VARCHAR2(10)    NOT NULL,
supplierName  VARCHAR2(30),
address      VARCHAR2(50),
contactNo    VARCHAR2(10),
PRIMARY KEY(supplierNo));

CREATE TABLE StoreOrderItem(
supplierNo      VARCHAR2(10)    NOT NULL,
orderNo        VARCHAR2(10)    NOT NULL,
name        VARCHAR2(20)    NOT NULL,
quantity      NUMBER(10),
cost        NUMBER(10,2),
PRIMARY KEY(supplierNo,orderNo,name),
FOREIGN KEY(supplierNo) REFERENCES Supplier(supplierNo),
FOREIGN KEY(orderNo) REFERENCES StoreOrder(orderNo),
FOREIGN KEY(name) REFERENCES Item(name));

查询是显示供应商的名称和地址，他们向商店提供了最多的食品

项目。



我的回答是

the query is "Display the supplier’s name and address who has supplied the highest number of food
items to the store."

and my answer is "

select s.supplierno, s.suppliername, s.address
from supplier s, storeorderitem st
where s.supplierno = st.supplierno
group by(s.supplierno, s.suppliername,s.address)
having sum(st.quantity)= (select max(sum(st1.quantity))
from storeorderitem st1
where st1.supplierno = s.supplierno
group by (st1.supplierno));

但我仍有错误可以帮助我解决

"

but still i have errors can u plz help me out

推荐答案

尝试这样......

Hi,

Try Like this....

SELECT s.supplierno, s.suppliername, s.address
FROM supplier S
INNER JOIN (SELECT supplierno,SUM(quantity) 'SupplierQty'
FROM storeorderitem GROUP BY supplierno) Q ON Q.supplierno=S.supplierno
WHERE Q.SupplierQty IN (SELECT MAX(T.TotalQuantity) 'MaxQuantity'
			FROM (SELECT supplierno, SUM(quantity) 'TotalQuantity'
			      FROM storeorderitem 
			      GROUP BY supplierno) T
			)

或你可以使用一个变量并分配最大总数量并在WHERE条件中使用该变量...检查逻辑。



问候，

GVPrabu

or you can use one variable and assign the maximum Total Quantity and use that variable in WHERE Condition... Check the logic.

Regards,
GVPrabu

这篇关于您好我在下面的查询中有错误PLZ可以帮助我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！