检索排名前10的行，并对第11行中的所有其他行求和

Question

我有以下查询，用于检索每个国家/地区的用户数；

SELECT C.CountryID AS CountryID, 
       C.CountryName AS Country, 
       Count(FirstName) AS Origin
FROM Users AS U
INNER JOIN Country AS C ON C.CountryID = U.CountryOfOrgin
GROUP BY CASE C.CountryName, 
              C.CountryID

我需要的是获得前10名，然后将所有其他用户汇总到一行的一种方法。我知道如何获得前10名，但我坚持将剩下的人排成一排。有没有简单的方法可以做到这一点？

例如，如果上面的查询返回17条记录，则显示前十名，并且其余7个国家/地区的用户总数应显示在第11行上。在该行11上，countryid为0，countryname
Others

谢谢你的帮助！

Answer 1

您没有指定排名前10位的方式，所以我假设最高的排名更高？

With TopItems As
    (
    SELECT C.CountryID AS CountryID
            , C.CountryName AS Country
            , Count(FirstName) AS Origin
            , ROW_NUMBER() OVER( ORDER BY Count(FirstName) DESC ) As Num
    FROM Users AS U
        JOIN Country AS C 
            ON C.CountryID = U.CountryOfOrgin
    GROUP BY C.CountryName, C.CountryID
    )
Select CountryId, Country, Origin
From TopItems
Where Num <= 10
Union ALL
Select 0, 'Others', Sum(Origin)
From TopItems
Where Num > 10