州內發票最多的供應商

我有表 Vendors(VendorName, VendorID,…) 和 Invoices(InvoiceID,…) 並且我正在嘗試找到在各自州內擁有最多發票的供應商。請批評我的工作：

SELECT Max(NumInvoicesState)
FROM 
(
  SELECT  VendorState,VendorName,
     Count(InvoiceID) AS NumInvoicesState 
  FROM VENDORS V 
  JOIN Invoices I
    on V.VendorID=I.VendorID
  GROUP by  VendorState, VendorName
) AS Alias

我的想法是首先進行查詢以獲取一個表格，其中包含按州列出的供應商列表以及每個供應商的發票數量。

我想將內表加入供應商，然後按 VendorState 分組，但這似乎不起作用。

我想我可以選擇 VendorName、VendorState，然後加入 Vendors，比如 VendorState，然後按 Vendors 分組。但是後來我得到了所有的供應商，而不僅僅是那些最大的供應商。

我認為內部查詢是正確的。

外部查詢中沒有匹配 GROUP BY 的 MAX 將給出整個數據集中的單個值。在您的範例中，數據集將是內部查詢。這不是您想要的，因此您必須添加group by state.

現在您遇到了供應商名稱的問題。它不能在選擇列表中，除非它也在 GROUP BY 中，但這會給出錯誤的結果。有必要分兩步進行。在外部查詢中只處理狀態：

Select state, max (..) as MaxByState
From (inner query)
Group by state

然後將其加入到MAX() 值的內部查詢中：

Select
 ...
from (
   Select state, max (..) as MaxByState
   From (inner query)
   Group by state
) as mbs
inner join (
 Inner query
) as iq
on mbs.state = iq.state
and mbs.MaxByState = iq.NumInvoicesState;

換句話說，這是每個州最多的發票。哪些供應商有這麼多發票？

如果兩個供應商在一個州擁有相同數量的發票，則會輸出兩行。

由於內部查詢是逐字重複的，因此最好將其呈現為非遞歸 CTE。

SQL Server 2012 內置了許多視窗函式。這裡最有用的一個是RANK ()。它只是做你認為它應該做的事情。

Select 
 RANK ( ) OVER (PARTITION BY state order by NumInvoicesState )
from (inner query) as a

這消除了上面所需的派生聚合值上的所有二階數據集對齊。它更易於閱讀和理解，因此更易於維護。

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