本文介紹了SQL為每個(gè)部門選擇具有最大銷售額的日期的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！
問(wèn)題描述
我為編寫棘手的查詢而苦惱。
我有下表：
對(duì)于每個(gè)部門，我要打印利潤(rùn)最大的日期；
我自己也試著想出這樣一個(gè)問(wèn)題：
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;
但問(wèn)題是部門打印了幾次，因?yàn)槲彝瑫r(shí)按名稱和日期分組。
我應(yīng)該如何修復(fù)它？
推薦答案
您可以嘗試以下方式-
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
這篇關(guān)于SQL為每個(gè)部門選擇具有最大銷售額的日期的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
