在SQL Server 2005中分组问题之前的排名

HI,

HI，

This should be easy but I don't understand enough about how grouping works.

这应该很容易，但我对分组的工作原理还不够了解。

Basically I have 2 tables "Categories" and "Items"

基本上我有2个表“类别”和“项目”

Categories
ID
CategoryName

类别ID CategoryName

Items
ID
CategoryID ItemName
Photo
Score

商品ID CategoryID ItemName照片分数

All I want to do is get 1 row for each category which contains the Category ID, the Category Name and the photo that belongs to the highest scoring item.

我想要做的就是为每个类别获得1行，其中包含类别ID，类别名称和属于最高得分项目的照片。

So I have tried joining the categories to the items and grouping by the CategoryID. Trouble is that I want to order the items so that the highest scoring items are at the top before it does the groupings to make sure that the photo is from the current highest scoring item in that category. If I select MAX(I.score) I can get the highest score but I'm not sure how to get accompanying photo as MAX(photo) will obviously give me the photo with the highest file name alphabetically.

所以我尝试通过CategoryID将类别加入项目和分组。麻烦的是，我想订购商品，以便在进行分组之前，最高得分的商品位于顶部，以确保照片来自该类别中当前得分最高的商品。如果我选择MAX（I.score）我可以获得最高分，但我不知道如何获得附带的照片，因为MAX（照片）显然会按字母顺序给我带有最高文件名的照片。

I hope I've explained that well.

我希望我已经解释得那么好。

5 个解决方案

#1

You could try something like (Full example)

你可以尝试一下（完整的例子）

DECLARE @Categories TABLE(
    ID INT,
    CategoryName VARCHAR(50)
)

DECLARE @Items TABLE(
    ID INT,
    CategoryID INT,
    ItemName VARCHAR(50),
    Photo VARCHAR(50),
    Score FLOAT
)

INSERT INTO @Categories (ID,CategoryName) SELECT 1, 'Cat1'
INSERT INTO @Categories (ID,CategoryName) SELECT 2, 'Cat2'

INSERT INTO @Items (ID,CategoryID,ItemName,Photo,Score) SELECT 1, 1, 'Item1', 'PItem1', 1
INSERT INTO @Items (ID,CategoryID,ItemName,Photo,Score) SELECT 2, 1, 'Item2', 'PItem2', 2
INSERT INTO @Items (ID,CategoryID,ItemName,Photo,Score) SELECT 3, 1, 'Item3', 'PItem3', 3

INSERT INTO @Items (ID,CategoryID,ItemName,Photo,Score) SELECT 4, 2, 'Item4', 'PItem4', 5
INSERT INTO @Items (ID,CategoryID,ItemName,Photo,Score) SELECT 5, 2, 'Item5', 'PItem5', 2

SELECT  *
FROM    (
            SELECT  c.ID,
                    c.CategoryName,
                    i.Photo,
                    i.Score,
                    ROW_NUMBER() OVER(PARTITION BY i.CategoryID ORDER BY i.Score DESC) RowID
            FROM    @Categories c INNER JOIN
                    @Items i ON c.ID = i.CategoryID
        ) CatItems
WHERE   RowID = 1

Using the ROW_NUMBER you can selet the items you require.

使用ROW_NUMBER，您可以选择所需的项目。

#2

You need to aggregate first and join back like this. (If you change grouping, you need to change JOIN)

您需要首先聚合并像这样加入。（如果更改分组，则需要更改JOIN）

SELECT
 ...
FROM
    (
    select
        max(Score) AS MaxScore,
        CategoryID
    FROM
        Items
    GROUP BY
        CategoryID
    ) M
    JOIN 
    Items I ON M.CategoryID = I.CategoryID AND M.MaxScore = I.Score
    JOIN
    Categories C ON I.CategoryID = C.CategoryID

#3

This is a pretty common problem, and one that SQL Server doesn't solve particularly well. Something like this should do the trick, though:

这是一个非常常见的问题，也是SQL Server无法解决的问题。但是这样的事情应该可以解决问题：

select
    c.ID,
    c.CategoryName,
    item.*

from Categories c

join (
    select 
        ID, 
        CategoryID, 
        ItemName, 
        Photo, 
        Score, 
        (row_number() over order by CategoryID, Score desc) - 
            (rank() over order by CategoryID) as rownum 

        from Items) item on item.CategoryID = c.CategoryID and item.rownum = 0

While there is no explicit group by clause, this (for practical purposes) groups the Categories records and gives you a joined statement that allows you to view any property of the highest scoring item.

虽然没有明确的group by子句，但是（出于实际目的）将类别记录分组并为您提供一个连接语句，允许您查看最高得分项目的任何属性。

#4

You can use row numbers to rank items per category:

您可以使用行号对每个类别的项目进行排名：

select *
from (
    select 
        row_number() over (partition by c.id order by i.score desc) rn
    ,    *
    from Categories c
    join Items i on c.ID = i.CategoryID
) sub
where rn = 1

In SQL 2005, you can't reference a row_number() directly in a where, so it's wrapped in a subquery.

在SQL 2005中，您不能直接在where中引用row_number（），因此它包含在子查询中。

#5

Exactly as you worded it: "the Category ID, the Category Name and the photo that belongs to the highest scoring item." -- Now here I surmise you really meant "...highest scoring item in that category", no?)

正如你所说的那样：“类别ID，类别名称和属于最高得分项目的照片。” - 现在我猜测你真的是“......那个类别中得分最高的项目”，不是吗？）

  Select CategoryID, c.Categoryname, Photo
  From items i Join Categoiries c
      On c.ID = i.CategoryId
  Where Score = (Select Max(Score) From Items
                 Where CategoryID = i.CategoryId)

If you really meant the highest scoring item on the whole items table, then just omit the predicate in the subquery

如果你真的意味着整个项目表中得分最高的项目，那么只需省略子查询中的谓词

  Select CategoryID, c.Categoryname, Photo
  From items i Join Categoiries c
      On c.ID = i.CategoryId
  Where Score = (Select Max(Score) From Items)

Both these queries will return multiple rows per group if there are more than one item in the defined group which tie for highest score..

如果定义的组中有多个项目与最高分数相关，则这两个查询都将返回每组多行。

#1