Server :从前3类中选择前3项

Question

我正在尝试编写一个查询，以接收来自前3大类的前3项的单个数据集。

顶级类别将按照按类别分组的ItemScore列的总和排序，例如，类别1的得分为23，类别2的得分为22。

然后，该类别中的每一项按其ItemScore降序排序。

以下是原始数据的列表。

​

​

这是一个预期结果的例子。

​

​

我希望这是合理的！

更新：

这是我一直在使用的脚本和一些测试数据；

CREATE TABLE [dbo].[Test]
(
    [CategoryID] [int] NULL,
    [CategoryName] [varchar](100) NULL,
    [ItemID] [int] NULL,
    [ItemName] [varchar](100) NULL,
    [ItemScore] [int] NULL
)

INSERT Test (CategoryID, CategoryName, ItemID, ItemName, ItemScore) 
VALUES (1, 'Category 1', 1, 'Item 1', 1),
       (1, 'Category 1', 2, 'Item 2', 2),
       (1, 'Category 1', 3, 'Item 3', 7),
       (1, 'Category 1', 4, 'Item 4', 1),
       (1, 'Category 1', 5, 'Item 5', 1),
       (2, 'Category 2', 6, 'Item 6', 1),
       (2, 'Category 2', 7, 'Item 7', 1),
       (2, 'Category 2', 8, 'Item 8', 9),
       (2, 'Category 2', 9, 'Item 9', 10),
       (2, 'Category 2', 10, 'Item 10', 2),
       (3, 'Category 3', 11, 'Item 11', 1),
       (3, 'Category 3', 12, 'Item 12', 1),
       (3, 'Category 3', 13, 'Item 13', 2), 
       (3, 'Category 3', 14, 'Item 14', 1),
       (3, 'Category 3', 15, 'Item 15', 2),
       (4, 'Category 4', 16, 'Item 16', 5),
       (4, 'Category 4', 17, 'Item 17', 6),
       (4, 'Category 4', 18, 'Item 18', 3),
       (4, 'Category 4', 19, 'Item 19', 5),
       (4, 'Category 4', 20, 'Item 20', 1),
       (5, 'Category 5', 21, 'Item 21', 2),
       (5, 'Category 5', 22, 'Item 22', 8),
       (5, 'Category 5', 23, 'Item 23', 5),
       (5, 'Category 5', 24, 'Item 24', 3),
       (5, 'Category 5', 25, 'Item 25', 4)

SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY t.CategoryID ORDER BY t.ItemScore DESC) AS RowNumber
FROM 
    Test t

Answer 1

这个怎么样：

• 首先计算CategoryScore (每个类别的ItemScore之和)
• 然后，根据类别和项目的得分添加“评级”。
• 最后，只返回两个评级最多为3的记录：

如果将其放在查询中，它可能如下所示：

WITH
  summing AS (
    SELECT CategoryID, CategoryName, ItemID, ItemName, ItemScore
      , SUM(ItemScore) OVER (PARTITION BY CategoryID) AS CategoryScore
    FROM Test
  ),
  rating AS (
    SELECT CategoryID, CategoryName, ItemID, ItemName, ItemScore
      , DENSE_RANK() OVER (ORDER BY CategoryScore DESC, CategoryID) AS GrpNumber
      , ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ItemScore DESC) AS RowNumber
    FROM summing
  )
SELECT CategoryID, CategoryName, ItemID, ItemName, ItemScore, RowNumber
FROM rating
WHERE GrpNumber <= 3
  AND RowNumber <= 3
ORDER BY GrpNumber, RowNumber;

Answer 2

您可以尝试将查询写成子查询，然后将RowNumber 1转到3

SELECT * FROM (
  SELECT
      *,
      Row_NUMBER() OVER (PARTITION BY t.CategoryID ORDER BY t.ItemScore DESC) AS RowNumber
  FROM Test t
) t1
where RowNumber <=3

木琴