将两个不同表中的两列相乘后找到最大值 - SQL

Question

我正在尝试找到正确的方式来在不同表中将 2 列相乘后找到最大值： 订单(OID, PID, Quantity, Date)产品(PID, Pname, Price, Category, Subcategory). 我需要找到哪个 OID 的总订单金额最高。 我试过这个：

SELECT OID, OrderAmount
FROM Orders
WHERE OrderAmount=(SELECT MAX(OrderAmount) from Orders
(SELECT Orders.OID, OrderAmount=Products.Price*Orders.Quantity
FROM Orders
INNER JOIN Products ON Orders.PID=Products.PID); 

也许有更有效的解决方案？ 提前感谢大家！

Answer 1

方法1：Aggregate函数

SELECT TOP 1 OID, MAX(OrderAmount) MaxOrderAmount
FROM (
SELECT Orders.OID, OrderAmount = Products.Price * Orders.Quantity
FROM Orders INNER JOIN Products ON Orders.PID=Products.PID
) X GROUP BY OID

方法1：Row_Number概念

SELECT TOP 1 OID, OrderAmount AS MaxOrderAmount
FROM (
SELECT Orders.OID, OrderAmount = Products.Price * Orders.Quantity
ROW_NUMBER() OVER(ORDER BY Products.Price * Orders.Quantity) RN
FROM Orders INNER JOIN Products ON Orders.PID=Products.PID
) X WHERE RN = 1