sql - 我必须得到查询结果中的第二大计数

Question

我正在使用一个测试数据库 Advetureworks，我想在结果中获得第二高的计数，但我没有得到它。

我必须对以下查询进行哪些更改才能获得所需的结果？

select pa.City,psp.Name,COUNT(he.EmployeeID) as emp_count
from HumanResources.EmployeeAddress hea
join HumanResources.Employee he on he.EmployeeID=hea.EmployeeID
join Person.Contact pc on pc.ContactID=he.ContactID
join Person.Address pa on pa.AddressID=hea.AddressID
join Person.StateProvince psp on psp.StateProvinceID=pa.StateProvinceID
where COUNT(he.EmployeeID) < (select max(count(he.employeeid)) from HumanResources.Employee)

group by pa.City,psp.Name

Answer 1

你可以使用排名功能，试试这样:

;WITH a AS (
    select pa.City,psp.Name,COUNT(he.EmployeeID) as emp_count
    from HumanResources.EmployeeAddress hea
    join HumanResources.Employee he on he.EmployeeID=hea.EmployeeID
    join Person.Contact pc on pc.ContactID=he.ContactID
    join Person.Address pa on pa.AddressID=hea.AddressID
    join Person.StateProvince psp on psp.StateProvinceID=pa.StateProvinceID
    group by pa.City,psp.Name   
), b AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY emp_count DESC) num
    FROM    a
)
SELECT  *
FROM    b
WHERE   b.num = 2