sql - 查询将计算文本值并返回具有最低计数值（min）的行？

Question

想知道是否有人可以协助进行此查询，我需要编写一个查询来计算商店，基于该州位置，然后显示该国家/地区计数最低的州位置。每个国家代码只能代表一次。

这就是我到目前为止所拥有的：-

select
  CountryRegionCode]
  , [StateProvinceCode]
  , COUNT(PSP.StateProvinceID) as [No. of Stores]
from [Sales].[Store] as SS
  inner join [Person].[BusinessEntityAddress] as PBEA on SS.BusinessEntityID = PBEA.BusinessEntityID
  inner join [Person].[Address] as PA on PBEA.AddressID = PA.AddressID
  inner join [Person].[StateProvince] as PSP on PA.StateProvinceID = PSP.StateProvinceID
group by 
  CountryRegionCode
  ,StateProvinceCode
having
  count(PSP.StateProvinceID) = 
   (select min(a.cnt)
     from (select count(PSP.StateProvinceID) as cnt from [Sales].[Store] as SS2
       inner join [Person].[BusinessEntityAddress] as PBEA2 on SS2.BusinessEntityID = PBEA2.BusinessEntityID
       inner join [Person].[Address] as PA2 on PBEA2.AddressID = PA2.AddressID
       inner join [Person].[StateProvince] as PSP2 on pa2.StateProvinceID = PSP2.StateProvinceID
  group by 
    CountryRegionCode) as a)
  order by CountryRegionCode

我已附加当前输出，如何限制输出仅显示以黄色突出显示的行？例如，每个国家/地区商店数量最少的州。

（我不允许使用 top 子句）

提前致谢

标签： sqlcountminhaving-clause

Answer 1

使用窗口函数：

select cs.*
from (select CountryRegionCode, StateProvinceCode,
             count(*) as num_stores,
             rank() over (partition by CountryRegionCode order by count(*) desc) as seqnum
      from [Sales].[Store] ss join
           [Person].[BusinessEntityAddress] pbea
      pbeaon ss.BusinessEntityID = pbea.BusinessEntityID inner join
         [Person].[Address] pa
         on pbea.AddressID = pa.AddressID inner join
         [Person].[StateProvince] psp
         on pa.StateProvinceID = psp.StateProvinceID
      group by CountryRegionCode, StateProvinceCode
     ) cs
where seqnum = 1;

如果有关系，这将返回一个国家/地区内具有最大值的所有州。如果您只想要一个，请使用row_number()而不是rank().