sql - 查找每天热销产品的产品id，根据总销售数量确定热销产品

Question

Adventureworks2008R2 数据库。

我想要的结果是每天卖出的 MaxQantity，例如 OrderDate 2007-09-01 的最大数量应该只有 96，但是我的查询在同一天给了我 3 个不同的结果，可能是因为它正在考虑还有时间戳

SELECT DISTINCT CAST(oh.OrderDate AS DATE) OrderDate, (od.ProductID),SUM(od.OrderQty) MAXOrderQty
FROM Sales.SalesOrderDetail od
Inner Join Sales.SalesOrderHeader oh
ON od.SalesOrderID = oh.SalesOrderID
GROUP BY od.ProductID, CAST(oh.OrderDate AS DATE), od.OrderQty
ORDER BY SUM(od.OrderQty) DESC 

Answer 1

您可以在 MAX Qty 之前编写 CTE 和自己 JOIN date

;WITH CTE(OrderDate,ProductID,MAXOrderQty) AS(
    SELECT  CAST(oh.OrderDate AS DATE) OrderDate,od.ProductID,SUM(od.OrderQty) MAXOrderQty
    FROM Sales.SalesOrderDetail od
    Inner Join Sales.SalesOrderHeader oh
    ON od.SalesOrderID = oh.SalesOrderID
    GROUP BY od.ProductID, CAST(oh.OrderDate AS DATE)
)
SELECT t1.* 
FROM CTE t1 INNER JOIN (
    select OrderDate,MAX(MAXOrderQty) 'MAXOrderQty'
    from CTE 
    GROUP BY OrderDate
)t2 on t1.OrderDate = t2.OrderDate and t1.MAXOrderQty = t2.MAXOrderQty