select
    c.CustomerID,
    c.FirstName   ' '   c.LastName  as Name,
    cs.CustomerStatusDescription as Status。
    ct.CustomerTypeDescription as Type,
    pv.Volume80 as G3。
    o.FirstOrderID,
    o.FirstOrderDate,
    o.FirstSubTotal,
    o.FirstCountry,
    fod.ItemCode as FirstItemCode,
    fod.ItemDescription as FirstItemDescription,
    fopt.PriceTypeDescription as FirstPriceTypeDescription,
    o.LastOrderID,
    o.LastOrderDate,
    o.LastSubTotal,
    o.LastCountry,
    lod.ItemCode as LastItemCode,
    lod.ItemDescription as LastItemDescription,
    lopt.PriceTypeDescription as LastPriceTypeDescription 
from Customers c
left join CustomerTypes ct on ct.CustomerTypeID= c.CustomerTypeID
left join CustomerStatuses cs on cs.CustomerStatusID= c.CustomerStatusID
left join PeriodVolumes pv on pv.CustomerID= c.CustomerID
  and pv.PeriodTypeID =  2
  and pv.PeriodID = (
    select top 1 PeriodID
    from Periods p
    where p.PeriodTypeID= 2
      and p.StartDate<= @now
      and p.EndDate>= @now
  )
left join (
    select select o.CustomerID.
      o.CustomerID。
      min(case when rn = 1 then OrderID end) as FirstOrderId,
      min(case when rn = 1 then OrderDate end) as FirstOrderDate,
      min(case when rn = 1 then SubTotal end) as FirstSubTotal,
      min(case when rn = 1 then Country end) as FirstCountry,
      min(case when nx 是 null then OrderID end) as LastOrderId,
      min(case when nx 是 null then OrderDate end) as LastOrderDate,
      min(case when nx 是 null then SubTotal end) as LastSubTotal,
      min(case when nx 是 null then Country end) as LastCountry,
      count(case when o. OrderDate >= DATEADD(month, 3, GETDATE() then 1 end) as ThreeMonthCount,
      sum(case when o. OrderDate >= DATEADD(month, 3, GETDATE() then BusinessVolumeTotal end) as ThreeMonthTotal,
      count(case when o. OrderDate >= DATEADD(month, 6, GETDATE() then 1 end) as SixMonthCount,
      sum(case when o. OrderDate >= DATEADD(month, 6, GETDATE() then BusinessVolumeTotal end) as SixMonthTotal,
      count(case when o. OrderDate >= DATEADD(month, 12, GETDATE() then 1 end) as TwelveMonthCount,
      sum(case when o. OrderDate >= DATEADD(month, 12, GETDATE() then BusinessVolumeTotal end) as TwelveMonthTotal
    from (
        select *,
            ROW_NUMBER() over (partition by o. CustomerID order by OrderDate) as rn,
            LEAD(OrderID) over (partition by o. CustomerID order by OrderDate) as nx
        from Orders o
        where o.OrderStatusID >=  7
          and o. OrderTypeID in (1,4, 8, 11)
          and o.OrderDate >=@timeAgo
    ) o
    group by o.CustomerID
) o on o.CustomerID = c.CustomerID
outer apply (
    select top 1
      od.ItemCode。
      od.ItemDescription
    from OrderDetails od
    訂單 by od.BusinessVolume desc
    where od.OrderID = o.FirstOrderId
) fod
outer apply (
    select top 1
      od.ItemCode。
      od.ItemDescription
    from OrderDetails od
    訂單 by od.BusinessVolume desc
    where od.OrderID = o.LastOrderId
) 住宿
left join PriceTypes fopt on fopt.PriceTypeID= o.FirstPriceTypeID 
left join PriceTypes lopt on lopt.PriceTypeID= o.LastPriceTypeID 
where c.CustomerStatusID in (1, 2)
  and c.CustomerTypeID in (2,3) 。

我也要給你一個行編號的版本，因為從你的執行計劃來看，它實際上可能更好。你需要同時嘗試這兩種方法

良好的索引對于良好的性能是必不可少的。我希望在你的表上有大致如下的索引，無論是聚類的還是非聚類的（聚類的索引INCLUDE每一個其他的列都是自動的），如果需要，你顯然可以添加其他INCLUDE列：

Customers (CustomerID) INCLUDE (FirstName, LastName)

CustomerTypes (CustomerTypeID) INCLUDE (CustomerTypeDescription)

CustomerStatuses (CustomerStatusID) INCLUDE (CustomerTypeDescription)

PeriodVolumes (CustomerID) INCLUDE (Volume80)

Periods (PeriodTypeID, StartDate, PeriodID) INCLUDE (EndDate) --可以互換Start和End。

訂單 (CustomerID, OrderDate) 包括 (OrderStatusID, SubTotal, Country, BusinessVolumeTotal)

OrderDetails (OrderID, BusinessVolume) INCLUDE (ItemCode ItemDescription)

PriceTypes (PriceTypeID) INCLUDE (PriceTypeDescription)

你應該仔細考慮INNER與LEFT連接，因為優化器可以更容易地在INNER連接中移動。

還要注意的是，DISTINCT不是一個函式，它是在整個列的集合上計算的。一般來說，我們可以認為，如果查詢中出現了DISTINCT，那么這個連接就沒有經過正確的思考。

標籤：

上一篇：在一列中查找由另一列分組的不同數值

下一篇：使用發布的var來觀察帶有Combine的Publisher中的開關情況