我有兩個表：
我需要找到銷量最高的產品名稱以及從中獲得的收益。
我寫的代碼：
SELECT *
FROM Products
WHERE ProductId = (SELECT ProductId
FROM
(SELECT
ProductId,
SUM(Quantity) AS total_order,
MAX(SUM(Quantity)) OVER () AS maxSm
FROM
Orders
GROUP BY
ProductId)
WHERE
total_order = maxSm)
但是有了這個，我只能找到銷量最高的產品名稱。你能告訴我如何才能找到僅從該產品中獲得的收益嗎？
uj5u.com熱心網友回復：
select top 1
a.name,
(b.total * a.price) as revenue
from
products a
left join (select productid, sum(quantity) as total group by productid) b
on a.productid = b.productid
order by
b.total desc
uj5u.com熱心網友回復：
您需要將派生表的結果加入到您的Products表中。
沒有實際的樣本資料，我無法測驗，但是以下應該是您需要的，或者至少非常接近：
select p.Name, o.total_order, o.total_order * p.Price as TotalValue
from (
select * from (
select ProductId,
Sum(Quantity) as total_order,
Max(Sum(Quantity)) over () as maxSm
from Orders
group byProductId
)t
where total_order = maxSm
)o join Products p on p.ProductId=o.ProductId
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/353182.html