我有三个表(客户、订单、产品(我在产品表中做了一列,应该有客户总共订购了多少产品。这意味着我想知道客户提交的每种产品的订单数量。
我在OracleSQLDeveloper:中尝试过这个语句
select count(customer_code) into product.numberOfOredrs
from order
where order.prodact_name = product.product_name;
但不起作用。
产品表的列:product_name,矿石数量。
订单表的列:Id_ order,customer_code。
客户表的列:customer_code。
您似乎想要aggeration:
select customer_code, count(*) as no_of_product
from order o inner join
product p
on p.product_name = o.product_name
group by o.customer_code;
下面的查询将为您提供每个客户的每个产品的订单数。
SELECT C.Customer_code,p.product_name,COUNT(Id_Order) AS CountOfOrders
FROM Customer AS c
INNER JOIN Order AS o ON c.customer_code = o.customer_code
INNER JOIN Product AS p ON p.product_name = o.product_name
GROUP BY C.Customer_code,p.product_name;
如果您想更新product
表中的记录,则可以使用以下方法:
update product p
set p.numberOfOredrs = (select count(1)
from order o
where o.product_name = p.product_name)