本文介绍了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为每个部门选择具有最大销售额的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！