本文介紹了SQL為每個部門選擇具有最大銷售額的日期的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我為編寫棘手的查詢而苦惱。
我有下表：
對于每個部門，我要打印利潤最大的日期；
我自己也試著想出這樣一個問題：
Select DISTINCT(Name), Date_sale, MAX(A) as B FROM (SELECT
Departments.Name, SALES.Date_sale, SUM(GOODS.Price * SALES.Quantity)
AS A FROM DEPARTMENTS, GOODS, SALES
WHERE DEPARTMENTS.Dept_id = GOODS.Dept_id AND GOODS.Good_id =
SALES.Good_id GROUP BY DEPARTMENTs.Name, SALES.Date_sale)
GROUP BY Name, Date_sale;
但問題是部門打印了幾次，因為我同時按名稱和日期分組。
我應該如何修復它？
推薦答案
您可以嘗試以下方式-
with cte as
(
SELECT
Departments.Name, SALES.Date_sale, SUM(GOODS.Price * SALES.Quantity)
AS profit FROM DEPARTMENTS inner join GOODS on DEPARTMENTS.Dept_id = GOODS.Dept_id
inner join SALES on GOODS.Good_id = SALES.Good_id
GROUP BY DEPARTMENTs.Name, SALES.Date_sale
)A
select * from cte a
where profit =
(select max(profit) from cte b on a.department=b.department)
或者您可以使用row_number()
select * from
(
select *, row_number() over(partition by department oder by profit desc) as rn
from cte
)A where rn=1
這篇關于SQL為每個部門選擇具有最大銷售額的日期的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
