SQL 子查询/连接

Question

好吧，我是 SQL 的初学者，所以如果这个问题对你来说太新手，请原谅。
在 Northwind(我相信每个人都知道 Northwind) - 我试图每个月获得 1997 年销量(按数量)最多和销量最少的产品
(使用订单、订单详情、产品表)

谢谢

更新 :
这是我到目前为止

Select p.productname, 
       Datepart(MM,o.OrderDate), 
       od.Quantity 
from orders o 
inner join [Order Details] od 
  on o.OrderID = od.OrderID 
inner join Products p 
  on p.ProductID=od.ProductID 
Where YEAR(o.OrderDate)=1997 
Group by Datepart(MM,o.OrderDate), od.Quantity,p.productname

Answer 1

我想出了一个解决方案来解决你的问题。
这是一个带有 3 个子选择的 select 语句，但它可以工作，并且在同一行上有最畅销和最不畅销的产品:

select  
    a.month,
    (select top 1 
            p.ProductName as pid 
        from Northwind.dbo.[Order Details] od1 
            left join Northwind.dbo.Orders o on  o.OrderID = od1.OrderID 
            left join Northwind.dbo.Products p on p.ProductID = od1.ProductID 
        where 
            YEAR(o.OrderDate)=1997 and 
            DATEPART(MM,o.OrderDate) = a.month 
        group by 
            p.ProductName,
            DATEPART(MM,o.OrderDate) 
        having 
            sum(od1.Quantity) = MAX(a.qty)), 
    max(a.qty) as maxQty,
    (select top 1 
            p.ProductName as pid 
        from Northwind.dbo.[Order Details] od1 
            left join Northwind.dbo.Orders o on  o.OrderID = od1.OrderID 
            left join Northwind.dbo.Products p on p.ProductID = od1.ProductID 
        where 
            YEAR(o.OrderDate)=1997 and 
            DATEPART(MM,o.OrderDate) = a.month 
        group by 
            p.ProductName,
            DATEPART(MM,o.OrderDate) 
        having 
            sum(od1.Quantity) = MIN(a.qty)), 
    min(a.qty) as minQty
from 
    (select 
            p.ProductName as pid,
            DATEPART(MM,o.OrderDate) as month, 
            sum(od1.Quantity) as qty 
        from Northwind.dbo.[Order Details] od1 
            left join Northwind.dbo.Orders o on  o.OrderID = od1.OrderID 
            left join Northwind.dbo.Products p on p.ProductID = od1.ProductID 
        where 
            YEAR(o.OrderDate)=1997 
        group by 
            p.ProductName,
            DATEPART(MM,o.OrderDate)) as a 
group by 
    a.month

我确信使用一些临时表可以做一些更聪明的事情