检索前 10 行并对第 11 行中的所有其他行求和 [英] Retrieving Top 10 rows and sum all others in row 11

问题描述

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

I have the following query that retrieve the number of users per country;

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 名，但我坚持将剩余的排在一行.有什么简单的方法吗?

What I need is a way to get the top 10 and then sum all other users in a single row. I know how to get the top 10 but I`m stuck on getting the remaining in a single row. Is there a simple way to do it?

例如，如果上述查询返回 17 条记录，则显示前 10 条记录，其余 7 个国家/地区的用户总数应显示在第 11 行.在第 11 行，countryid 将为 0，countryname Others

For example if the above query returns 17 records the top ten are displayed and a sum of the users from the 7 remaining country should appear on row 11. On that row 11 the countryid would be 0 and countryname Others

感谢您的帮助！

推荐答案

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

You did not specify how you are ranking the top 10 so I'm assuming the highest counts are ranked higher?

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

这篇关于检索前 10 行并对第 11 行中的所有其他行求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！