SQL 选择每个部门最大销售额的日期

Question

这个问题在这里已经有了答案:





Fetch the row which has the Max value for a column

(35 个回答)



Oracle SQL query: Retrieve latest values per group based on time [duplicate]

(2 个回答)



Get value based on max of a different column grouped by another column [duplicate]

(1 个回答)



SQL: getting the max value of one column and the corresponding other columns [duplicate]

(2 个回答)


1年前关闭。




我对编写一个棘手的查询感到困扰。

我有下表:



对于每个部门，我想打印利润最大的日期；

我尝试自己提出这样的查询:

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;

但问题是部门被打印了几次，因为我按名称和日期分组。



我该如何解决？

Answer 1

您可以尝试以下方式-

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