SQL Server查询按列中的最大值分组

****Update:**

using the Rank() over partition syntax available in MS SQL Server 2005 does indeed point me in the right direction, it (or maybe I should write "I") is unable to give me the results I need without resorting to enumerating rows in code.

使用MS SQL Server 2005中可用的Rank()over分区语法确实指向了正确的方向,它(或者我应该写“I”)无法向我提供我需要的结果而无需在代码中枚举行。

For example, if we select TOP (1) of rank, I get only one value, ie., slot 1. If I use MAX(), then I get the top ranked value for each slot...which, in my case, doesn't work, because if slot 2's top value is NULL, but it's next to MAX value is non-empty, that is the one I want.

例如,如果我们选择排名的TOP(1),我只得到一个值,即插槽1.如果我使用MAX(),那么我得到每个插槽的排名最高的值...在我的情况下,不起作用,因为如果插槽2的顶部值为NULL,但它旁边的MAX值是非空的,那就是我想要的那个。

So, unable to find a completely T-SQL solution, I've resorted to filtering as much as possible in SQL and then enumerating the results in code on the client side.

因此,无法找到完全的T-SQL解决方案,我已经尽可能地在SQL中进行过滤,然后在客户端的代码中枚举结果。

Original:

I've been hitting advanced T-SQL books, * and google trying to figure out how to handle this query either by using pivots or by using analytic functions. So far, I haven't hit on the right combination.

我一直在使用高级T-SQL书籍,*和谷歌试图通过使用枢轴或使用分析函数来弄清楚如何处理这个查询。到目前为止,我还没有找到合适的组合。

I have schedules that are ranked (higher value, greater precedence). Each schedule has a playlist of a certain number of numbered slots with files.

我有排名(更高的值,更高的优先级)的计划。每个时间表都有一个带有文件的一定数量编号插槽的播放列表。

What I need to do, is line up all the schedules and their associated playlists, and for each slot, grab the file from the schedule having the highest ranking value.

我需要做的是排队所有时间表及其关联的播放列表,并且对于每个插槽,从具有最高排名值的时间表中获取文件。

so, if I had a query for a specific customer with a join between the playlists and the schedules, ordered by Schedule.Rank DESC like so:

所以,如果我通过播放列表和日程表之间的连接查询特定客户,请按Schedule.Rank DESC排序,如下所示:

PlaylistId   Schedule.Rank    SlotNumber    FileId
100               100             1          1001
100               100             2          NULL
100               100             3          NULL
200                80             1          1101
200                80             2          NULL 
200                80             3          NULL
300                60             1          1201
300                60             2          NULL
300                60             3          2202
400                20             1          1301
400                20             2          2301
400                20             3          NULL

From this, I need to find the FileId for the MAX ranked row per slotnumber:

从这里,我需要找到每个槽号的MAX排序行的FileId:

SlotNumber   FileId    Schedule.Rank
1             1001         100
2             2301          20
3             2202          60

Any ideas on how to do this?

关于如何做到这一点的任何想法?

Table Definitions below:

表定义如下:

CREATE TABLE dbo.Playlists(
    id int NOT NULL)

CREATE TABLE dbo.Customers(
    id int NOT NULL,
    name nchar(10) NULL)

CREATE TABLE dbo.Schedules(
    id int NOT NULL,
    rank int NOT NULL,
    playlistid int NULL,
    customerid int NULL)

CREATE TABLE dbo.PlaylistSlots(
    id int NOT NULL,
    slotnumber int NOT NULL,
    playlistid int NULL,
    fileid int NULL)

3 个解决方案

#1

SELECT slotnumber, fileid, rank
FROM
(
    SELECT slotnumber, fileid, Schedules.rank, RANK() OVER (PARTITION BY slotnumber ORDER BY Schedules.rank DESC) as rankfunc
    FROM Schedules
    INNER JOIN PlaylistSlots ON Schedules.playlistid = PlaylistSlots.playlistid
) tmp
WHERE rankfunc = 1

#2

Have you looked at SQL Server's (2005 onwards) PARTITION and RANK features?

您是否看过SQL Server(2005年起)的PARTITION和RANK功能?

#3

select SlotNumber, FileId, ScheduleRank
FROM intermediateTable a, 
(
   SELECT SlotNumber, Max(Schedule.Rank) as MaxRank
   FROM intermediateTable O
   WHERE FileId is not null GROUP BY SlotNumber) b
WHERE b.SlotNumber = a.SlotNumber and b.MaxRank = a.Rank

This query uses the intermediate output to build the final output.
Does this help?

此查询使用中间输出来构建最终输出。这有帮助吗?