相關子查詢查找總數大於狀態平均值

我有表供應商（VendorName、VendorState、….）和發票（InvoiceID、InvoiceTotal、…）。我想獲得大於平均狀態 InvoiceTotal 的發票（作為 InvoiceId）。

我知道我首先找到每個州的平均總數：

SELECT  VendorState, Avg(InvoiceTotal) AS AvgStateInvoice
from Invoices I join Vendors V on V.VendorID= I.VendorID 
group by VendorState

所以我現在有按州列出的 avg InvoiceTotal 列表。現在我需要弄清楚：

如何進行外部查詢以選擇大於州平均水平的發票，這就是我迷路的地方，因為我不記得進行比較的語法。我想它會是這樣的：

SELECT InvoiceId from Invoices where InvoiceTotal > .....?

請問有什麼想法嗎？

你可以這樣做：

select * 
from 
   dbo.Invoice I1 
   join dbo.Vendors V1 on V1.VendorID = I1.VendorID
where 
   I1.InvoiceTotal > (
       SELECT 
           Avg(I2.InvoiceTotal)
       from 
           dbo.Invoices I2 
           join dbo.Vendors V2 on V2.VendorID = I2.VendorID
       where 
           V1.VendorState = V2.VendorState 
   );

或者使用視窗函式，這可能會更快，因為它不需要額外的連接：

SELECT X.*
from (
   select
       *,
       Avg(I.InvoiceTotal) over (partition by V.VendorState) as AvgInv
   from 
       dbo.Invoices I
       join dbo.Vendors V on V.VendorID = I.VendorID
) X
where
   X.InvoiceTotal > X.AvgInv;

視窗函式選項可能不會更快。儘管它在查詢中保存了一個連接，但執行計劃將具有一個子表達式假離線（帶有兩個額外的連接）。需要表假離線來計算和保存目前分區的平均值。每個分區重播一次假離線結果。

引用自：https://dba.stackexchange.com/questions/137834