SQL Server : select the largest order total from multiple customers with multiple orders, 并且每个订单上有多个项目

Question

我真的被一个问题困住了，需要一点帮助。这是问题陈述:

"编写查询，显示所有客户、所有订单总数、订单数量、每个订单的平均总数、每个订单的平均商品数(带小数点)、最大订单总数和每个客户的最小订单总数。显示每个客户，即使客户没有下订单。”

这些是表格:

the lovely tables

到目前为止，我已经挂断了最大订单总数。我正在考虑对最高和最低订单总数进行子查询，但我无法使其工作。

SELECT
     TC.intCustomerID
    ,TC.strLastName + ',' + ' ' + TC.strFirstName AS strCustomerName
    ,ISNULL(SUM( TCOI.intQuantity * TI.monPrice), 0) AS monOrderTotals
    ,COUNT(DISTINCT TCO.intOrderIndex) AS intNumberOfOrders
    ,ISNULL(SUM(TCOI.intQuantity * TI.monPrice) / COUNT(DISTINCT TCO.intOrderIndex), 0) AS monAverageOrderTotals
    ,(SELECT MAX(TCOI.intQuantity * TI.monPrice)
      FROM TItems AS TI, TCustomerOrderItems AS TCOI
      WHERE TI.intItemID = TCOI.intItemID 
      -- Cross-query join with two columns
      -- AND TC.intCustomerID = TCOI.intCustomerID 
      -- AND TCO.intOrderIndex = TCOI.intOrderIndex 
      ----GROUP BY 
      -- TCOI.intCustomerID 
      --,TCOI.intOrderIndex 
     ) AS monMostExpensiveOrder
FROM
    TCustomers AS TC
LEFT OUTER JOIN 
    TCustomerOrders AS TCO ON (TC.intCustomerID = TCO.intCustomerID)
LEFT OUTER JOIN 
    TCustomerOrderItems AS TCOI ON (TCO.intOrderIndex = TCOI.intOrderIndex)
LEFT OUTER JOIN 
    TItems AS TI ON (TCOI.intItemID = TI.intItemID)
GROUP BY
     TC.intCustomerID
    ,TC.strLastName
    ,TC.strFirstName

任何见解将不胜感激。

Answer 1

对我来说，使用 common table expression当您使用派生表(从子查询中选择)时，大大有助于使代码更易于阅读和编写。

我认为这应该涵盖您正在尝试做的事情，但我不确定您想用哪种方式来计算每个订单的平均商品(按不同商品的数量或商品的数量):

with cte as (
  select 
      tc.intCustomerId
    , tc.strFirstName
    , tc.strLastName
    , tcoi.intOrderIndex
    , TotalPrice = isnull(sum(tcoi.intQuantity * ti.monPrice), 0 )
    , ItemCount  = count(*)
    , TotalItemQuantity = sum(tcoi.intQuantity)
  from TCustomers tc
    left join tCustomerOrderItems as tcoi
      on tc.intCustomerId = tcoi.intCustomerId
    left join tItems as ti
      on ti.intItemID = tcoi.intItemID 
)
select 
    intCustomerId
  , Name = isnull(strLastName+', ') + isnull(strFirstName,'')
  , countOrders   = count(intOrderIndex)
  , sumTotalPrice = sum(TotalPrice)
  , minTotalPrice = min(TotalPrice)
  , maxTotalPrice = max(TotalPrice)
  , avgTotalPrice = avg(TotalPrice)
  , avgItemCount  = (sum(ItemCount)+.0)/nullif(count(intOrderIndex),0)
  , avgItemQuant  = (sum(TotalItemQuantity)+.0)/nullif(count(intOrderIndex),0)
from cte
group by 
   intCustomerId
 , strFirstName
 , strLastName

---

要删除 cte 部分，您只需将查询移动到 from 即可。

select 
    intCustomerId
  , Name = isnull(strLastName+', ') + isnull(strFirstName,'')
  , countOrders   = count(intOrderIndex)
  , sumTotalPrice = sum(TotalPrice)
  , minTotalPrice = min(TotalPrice)
  , maxTotalPrice = max(TotalPrice)
  , avgTotalPrice = avg(TotalPrice)
  , avgItemCount  = (sum(ItemCount)+.0)/nullif(count(intOrderIndex),0)
  , avgItemQuant  = (sum(TotalItemQuantity)+.0)/nullif(count(intOrderIndex),0)
from (
  select 
      tc.intCustomerId
    , tc.strFirstName
    , tc.strLastName
    , tcoi.intOrderIndex
    , TotalPrice = isnull(sum(tcoi.intQuantity * ti.monPrice), 0 )
    , ItemCount  = count(*)
    , TotalItemQuantity = sum(tcoi.intQuantity)
  from TCustomers tc
    left join tCustomerOrderItems as tcoi
      on tc.intCustomerId = tcoi.intCustomerId
    left join tItems as ti
      on ti.intItemID = tcoi.intItemID 
  ) as cte
group by 
   intCustomerId
 , strFirstName
 , strLastName