我很难编写棘手的查询。
我有下表：
我要为每个部门打印利润最大的日期；
我尝试自己提出这样的查询：
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
您可以使用ROW_NUMBER进行书写，这将为部门按日期分组的每个日期的总计数提供一个数字，如下所示，然后您可以使用rn = 1来获得最高的销售日期
SELECT NAME,DATE_SALE,A
FROM
(
SELECT
DEPARTMENTS.NAME,SALES.DATE_SALE,ROW_NUMBER() OVER(
PARTITION BY DEPARTMENTS.NAME
ORDER BY SUM(GOODS.PRICE * SALES.QUANTITY) DESC NULLS LAST
) AS RN,SUM(GOODS.PRICE * SALES.QUANTITY) AS A
FROM DEPARTMENTS
JOIN GOODS ON ( DEPARTMENTS.DEPT_ID = GOODS.DEPT_ID )
JOIN SALES ON ( GOODS.GOOD_ID = SALES.GOOD_ID )
GROUP BY DEPARTMENTS.NAME,SALES.DATE_SALE
)
WHERE RN = 1;
重要，请使用标准的ANSI联接。
干杯！
,我将在此处使用join-s，因为需要从通过第三个表链接的2个表中提取信息。
类似这样的东西（但是我没有测试这个查询，只是建议一种方法）：
Select department.name as dept,MAX(sales.quantity) as max_sales,sales.date_sale
from goods
Left outer join departments on departments.dept_id = goods.dept_id
Left outer join sales on sales.good_id = goods.good_id
Group by dept