时间戳

首页 > 解决方案 > SQL为每个部门选择最大销售额的日期

sql - 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;

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

结果

我应该如何解决它？

标签： sqloraclegreatest-n-per-group

解决方案

您可以尝试以下方式-

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

推荐阅读