t-sql 按类别分组并获取前 n 个值 [英] t-sql group by category and get top n values

问题描述

想象一下我有这张桌子:

Imagine I have this table:

Month | Person | Value
----------------------
Jan   | P1     | 1
Jan   | P2     | 2
Jan   | P3     | 3
Feb   | P1     | 5
Feb   | P2     | 4
Feb   | P3     | 3
Feb   | P4     | 2
...

如何构建 t-sql 查询以获取前 2 个值行和第三个值行的总和?

How can I build a t-sql query to get the top 2 value rows and a third with the sum of others?

像这样:

RESULT: 
Month | Person | Value
----------------------
Jan   | P3     | 3
Jan   | P2     | 2
Jan   | Others | 1 -(sum of the bottom value - in this case (Jan, P1, 1))
Feb   | P1     | 5
Feb   | P2     | 4
Feb   | Others | 5 -(sum of the bottom values - in this case (Feb, P3, 3) and (Feb, P4, 2))

谢谢

推荐答案

假设您使用的是 SQL Server 2005 或更高版本，使用 CTE 就可以解决问题.

In the assumption you are using SQL Server 2005 or higher, using a CTE would do the trick.

• 将 ROW_NUMBER 附加到每一行，从最大值开始，每个月重置.
• SELECT 此查询中每个月的前 2 行 (rownumber <= 2)
• UNION 与剩余的行 (rownumber > 2)

• Attach a ROW_NUMBER to each row, starting with the highest value, resetting for each month.
• SELECT the top 2 rows for each month from this query (rownumber <= 2)
• UNION with the remaining rows (rownumber > 2)

SQL 语句

;WITH Months (Month, Person, Value) AS (
  SELECT 'Jan', 'P1', 1 UNION ALL
  SELECT 'Jan', 'P2', 2 UNION ALL
  SELECT 'Jan', 'P3', 3 UNION ALL
  SELECT 'Feb', 'P1', 5 UNION ALL
  SELECT 'Feb', 'P2', 4 UNION ALL
  SELECT 'Feb', 'P3', 3 UNION ALL
  SELECT 'Feb', 'P4', 2
),
q AS (
  SELECT  Month
          , Person
          , Value
          , RowNumber = ROW_NUMBER() OVER (PARTITION BY Month ORDER BY Value DESC)
  FROM    Months
)
SELECT  Month
        , Person
        , Value
FROM    (        
          SELECT  Month
                  , Person
                  , Value
                  , RowNumber
          FROM    q
          WHERE   RowNumber <= 2 
          UNION ALL
          SELECT  Month
                  , Person = 'Others'
                  , SUM(Value)
                  , MAX(RowNumber)
          FROM    q
          WHERE   RowNumber > 2        
          GROUP BY
                  Month 
        ) q                          
ORDER BY
        Month DESC
        , RowNumber

Kudo 去找 Andriy 教我一些新技巧.

这篇关于t-sql 按类别分组并获取前 n 个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！