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